SQL: Getting the Minimum Count?

[pcmattman]

I have a table in a database, with a 'grade' field. I need to find the 'grade' with the least number of people in it. At the moment, this is my query:

Code: Select all

select min(count(*)) from student

Obviously, this doesn't work. I just can't seem to get the minimum count of a group of data.

This query works to tell me all the grades and the count of students in each:

Code: Select all

select grade,count(*) from student group by grade

But I need to get the minimum count... Any ideas?

[AJ]

Hi,

It's a while since I have used SQL statements, but could you use the having clause to do something like that? (HAVING MIN(count(grade))), or something like that?

Cheers,
Adam

[pcmattman]

Hmmm... I looked at that, didn't really help much.

What I have is a table of 25 students, each has a grade. I'm trying to find out which grade has the lowest count of students.

I've worked at this for about 3 hours now, that's far too long for a (simple) SQL query...

Edit:
SQL query

Code: Select all

select grade,count(*) from student group by grade

Returns table:

Code: Select all

grade	count(*)
4	5
5	7
6	6
7	7

[AJ]

Ok - I'll try one more suggestion then someone with more know-how can try, but how about using:

Code: Select all

SELECT grade,COUNT(*) FROM student WHERE COUNT(*) = MIN(COUNT(*))

I'll go away now

Cheers,
Adam

[pcmattman]

Nope...

Code: Select all

Invalid use of group function

Edit: tried to get smart with it,

Code: Select all

select grade,count(*)
from student
group by grade
having
min(select count(*) from student group by grade)
<
max(select count(*) from student group by grade)

No luck (syntax error this time). This is why I prefer C for such things...

Edit 2:

Ok,

Code: Select all

select grade,count(*)
from student
group by grade
having
count(*)=5

This gives me the correct answer. How can I do this without hard-coding the value?

[nick8325]

Code: Select all

select grade from student
group by grade
order by count(*)
limit 1

works here. It seems like a bit of a hack, but I don't know a nicer way of doing it.

[XCHG]

I don't know if I got the question right but here is the solution:

Code: Select all

SELECT COUNT(*) AS 'Count of the least Grade' FROM [Students] WHERE 
       [Grade] = (SELECT MIN([Grade]) FROM [Students])

I will put the complete code that I coded to create the whole table and stuff:

Code: Select all

USE [Master]
GO
IF (EXISTS(SELECT * FROM [SYSOBJECTS] WHERE [Name] = 'Students' AND TYPE = 'U'))
  BEGIN
    DROP TABLE [Students]
  END;
GO


CREATE TABLE [Students] (StudentID     INT PRIMARY KEY NOT NULL IDENTITY(0, 1),
                         StudentName   VARCHAR(50) NOT NULL UNIQUE,
                         Grade         INT NOT NULL);

GO
BEGIN TRANSACTION [Inserts]
  INSERT INTO [Students] ([StudentName], [Grade]) VALUES ('Name1', 100);
  INSERT INTO [Students] ([StudentName], [Grade]) VALUES ('Name2', 100);
  INSERT INTO [Students] ([StudentName], [Grade]) VALUES ('Name3', 50);
  INSERT INTO [Students] ([StudentName], [Grade]) VALUES ('Name4', 20);
  INSERT INTO [Students] ([StudentName], [Grade]) VALUES ('Name5', 20);
COMMIT TRANSACTION
GO

SELECT COUNT(*) AS 'Count of the least Grade' FROM [Students] WHERE 
       [Grade] = (SELECT MIN([Grade]) FROM [Students])

Coded in SQL Query Analyzer 2000.

[pcmattman]

@XCHG: that's correct, well done

Just shows, I can spend hours on something and you guys can do it in minutes.

[nick8325]

I think XCHG's code finds the number of people with the numerically smallest grade, rather than the number of people with the least common grade:

Code: Select all

> create table student(id int, grade int);
> insert into student values (1, 1);
> insert into student values (2, 3);
> insert into student values (3, 2);
> insert into student values (4, 2);
> insert into student values (5, 3);
> insert into student values (6, 3);
> insert into student values (7, 1);
> insert into student values (8, 1);
> select * from student order by grade;
id      grade
1       1
7       1
8       1
3       2
4       2
2       3
5       3
6       3
> select count(*) from student where grade = (select min(grade) from student);
count(*)
3
> select min(grade) from student;
min(grade)
1

[pcmattman]

It turns out that I was meant to use the 'All' aggregate function in a 'having' clause...

[Khumba]

Hey pcmattman, did you find a working query using having? This is the best I could come up with, but I shudder to think how inefficient it probably is:

Code: Select all

SELECT DISTINCT grade g FROM student WHERE
  (SELECT count(*) FROM student WHERE grade=g) =
  (SELECT min(z.cnt) FROM (SELECT count(*) cnt FROM student GROUP BY grade) z)