GROUP BY in Union MYSQL

Question

I am trying to get count of students per classroom who are present.

The conditions are :

1. All students from classrooms where ClassroomID is < 99 and have (DropTime <> '00:00' and PickupTime ='00:00') And Date is 20161111

2. All students from classrooms where ClassroomId is > 99 and have (DropTime='00:00') and Date is 20161111

I am using the following query in MySQL and it works fine and I get the correct result:

 SELECT 
    COUNT(a.Id) AS total, c.Name
FROM
    Attendance a
INNER JOIN
    Classroom c ON a.classroom = c.Id
WHERE
    DropDate = '20161111'
AND DropTime <> '00:00'
AND PickupTime = '00:00'
GROUP BY Name

except that when I try to do a union to get the results for another classroom (i.e ClassroomID > 99) it does not bring each classroom and only brings 1 classroom with total count. This is the union query I am using;

 SELECT 
SUM(total), Name
FROM
(SELECT 
    COUNT(a.Id) AS total, c.Name
FROM
    Attendance a
INNER JOIN Classroom c ON a.classroom = c.Id
WHERE
    DropDate = '20161111'
        AND DropTime <> '00:00'
        AND PickupTime = '00:00'
        AND c.Id < 99 
UNION ALL 
   SELECT 
    COUNT(a.Id) AS total, c.Name
FROM
    Attendance a
INNER JOIN Classroom c ON a.classroom = c.Id
WHERE
    DropDate = '20161111'
        AND PickupTime = '00:00'
        AND c.Id > 99) t
GROUP BY Name

Answer 1

Group by's have to be in place for each aggregation. Thus you need three group by's; 1 for each of the union statements as both are aggregating as well as one for the outer select... since you're aggregating there as well.

Put another way, each query must be able to operate on it's own before it can operate within another. Your two inline queries would fail in most RDBMS systems due to the missing group by's, however MYSQL's extended group by complicates this.

However, there may be a more efficient way to write the query... But here's a working version based on your inital attempt.

SELECT 
SUM(total), Name
FROM
(SELECT 
    COUNT(a.Id) AS total, c.Name
FROM
    Attendance a
INNER JOIN Classroom c ON a.classroom = c.Id
WHERE
    DropDate = '20161111'
        AND DropTime <> '00:00'
        AND PickupTime = '00:00'
        AND c.Id < 99 
GROUP BY c.name
UNION ALL 
   SELECT 
    COUNT(a.Id) AS total, c.Name
FROM
    Attendance a
INNER JOIN Classroom c ON a.classroom = c.Id
WHERE
    DropDate = '20161111'
        AND PickupTime = '00:00'
        AND c.Id > 99
GROUP BY c.name) t
GROUP BY t.name

This appears to be simplier and should achieve the same results (provided I didn't screw something up) and it should be faster as it doesn't have to generate 3 different sets and then combine them.

SELECT COUNT(a.Id) AS total
     , c.Name
FROM Attendance a
INNER JOIN Classroom c 
  ON a.classroom = c.Id
WHERE DropDate = '20161111' 
  AND ((DropTime <> '00:00' AND PickupTime = '00:00' AND c.Id < 99)
   OR (PickupTime = '00:00' AND c.Id > 99))
GROUP BY c.name