snowflakes74 snowflakes74 - 26 days ago 10
MySQL Question

GROUP BY in Union MYSQL

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

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