sql GROUP BY and HAVING not working

Question

I have this SQL query which is giving me a result which is not complete (half result). I want to retrieve all students info from the same class which is from the student table, then group them by class.

These are the queries code:

SELECT first_name, last_name, username 
FROM students 
GROUP BY class        
HAVING class IN(SS3);

SELECT first_name, last_name, class 
FROM students 
GROUP BY class
HAVING class = 'JSS1';

SELECT user_id, first_name, last_name, class, COUNT(*) AS total_students
FROM students 
GROUP BY class
HAVING class IN ('JSS1')

The last query also display just only one info, which is the first "jss1" class student name in the table but this time shows that there are 17 students who are in jss1

How do I manipulate the query to display all the names of the students in the same class?

This is the table structure:

CREATE TABLE IF NOT EXISTS `students` (
    `user_id` int(11) NOT NULL AUTO_INCREMENT,
    `class` varchar(10) NOT NULL,
    `first_name` varchar(30) NOT NULL,
    `last_name` varchar(50) NOT NULL,
    `username` varchar(20) NOT NULL,
    PRIMARY KEY (`user_id`)
)

Answer 1

If you want to list students in a given class do this:

SELECT first_name, last_name, class 
FROM students 
WHERE class = 'JSS1';

Query to list number of students in each class

SELECT class, count(user_id)
FROM students
GROUP BY class;

You see these two queries solves different purpose. group by clause is used to group related data together and off-course you should have all the columns mentioned in group by which you are selecting.

You may get list of students in particular class using group by as well and your query will go like this:

SELECT first_name, last_name, class 
FROM students 
GROUP BY first_name, last_name, class
HAVING class = 'JSS1';

notice that using group by in above query is unnecessary you can achieve the results easily using where clause as done in the first query.

Answer 2

The queries you have written make no sense, and would not parse in any other DBMS I know of. MySQL extends the standard SQL use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause., which can be both a blessing and a curse.

To assist, consider the following sample data

UserID      Class       First_name  
------------------------------------
1           A           Test 1  
2           A           Test 2  
3           B           Test 3  
4           B           Test 4  
5           C           Test 5  

Now, in your first query (adjusted to fit the data above)

SELECT first_name
FROM students 
GROUP BY class        
HAVING class IN('A');

We need to first think about the logical order of operations above:

1. FROM
2. WHERE
3. GROUP BY
4. HAVING 
5. SELECT

So the first thing to look at is the group by, since class is the grouping column you want to return one row per group, so you would have:

class  first_name
------------------
A       ???
B       ???
C       ???

The having clause now stipulates that it must be A, leaving:

class  first_name
------------------
A       ???

Now, this is the reason most DBMS fail, at this point you need to SELECT first_name, BUT you have two available values ("Test 1" and "test 2") but only one row to fill, and have given the engine no instruction about which one of the two to choose. The documentation states that the engine is free to choose any available value, and that order by will not have an effect on that, so you might get different for the same query depending on the execution plan.

While you are not really sure about GROUP BY, I would recommend that you enable ONLY_FULL_GROUP_BY.

So this is why you only get one row back, not all students. The answer as to how to get all students is a fairly simple one, just use WHERE:

SELECT first_name, last_name, username
FROM Students
WHERE Class IN ('JSS1');

If you wanted the same format of one row per class, but all students listed, then you might want GROUP_CONCAT:

SELECT  class, GROUP_CONCAT(CONCAT(First_name, ' ', last_name)) AS Students
FROM    Students
GROUP BY class;

Finally, you should only be USING having with aggregate functions. An example of this might be trying to find classes with 2 students in:

SELECT Class
FROM Students
GROUP BY Class
HAVING COUNT(*) = 2;

Answer 3

GROUP BY clause has applied when you want to group some informations about an aggregate functions (as the last query where you want to know the COUNT).

So when you have an aggregate function and if you want to show with it, scalar fields, these must be put in GROUP BY clause.

HAVING clause is used to apply a condition of aggregated value (for example, I want to know all rows with a COUNT > 1), I'll write: HAVING COUNT() > 1, so all your queries must to use a WHERE clause

Your queries:

WRONG

SELECT first_name, last_name, username 
FROM students 
GROUP BY class        
HAVING class IN(SS3);

BECOMES

SELECT first_name, last_name, username 
FROM students        
WHERE class IN (SS3);

WRONG

SELECT first_name, last_name, class 
FROM students 
GROUP BY class
HAVING class = 'JSS1';

BECOMES

SELECT first_name, last_name, class 
FROM students 
WHERE class = 'JSS1';

WRONG

SELECT user_id, first_name, last_name, class, COUNT(*) AS total_students
FROM students 
GROUP BY class
HAVING class IN ('JSS1')

BECOMES

SELECT user_id, first_name, last_name, class, COUNT(*) AS total_students
FROM students 
WHERE class IN ('JSS1')
GROUP BY class, user_id, first_name, last_name

Answer 4

if you need all the students of given class, why not use where clause

SELECT first_name, last_name, class 
FROM students 
WHERE class = 'JSS1';