CODEWITHSUNDEEP
phpmysqlsql
Joudicek Jouda
Joudicek Jouda

Reputation: 792

GROUP BY multiple conditions at once

I have a tables like this:

Users

+----+----------+-------------+                           
| id | name     | other_stuff |                           
+----+----------+-------------+                           
|  1 | John Doe |           x |                           
|  2 | Jane Doe |           y |                           
|  3 | Burt Olm |           z |                           
+----+----------+-------------+

Places 

+----+------------+-------------+                         
| id | name       | other_stuff |                         
+----+------------+-------------+                         
|  1 | Building A |           x |                         
|  2 | Building B |           y |                         
+----+------------+-------------+

Subjects 

+----+------------+-------------+                         
| id | name       | other_stuff |                         
+----+------------+-------------+                         
|  1 |       Math |           x |                         
|  2 |    English |           y |                         
+----+------------+-------------+

And a joining table:

PastLectures = lectures that took place 

+----+-----------+----------+------------+---------+------------+
| id | id_users  | id_place | id_subjects| length  | date       |
+----+-----------+----------+------------+---------+------------+
|  1 |         1 |        1 |          1 |      60 | 2015-10-25 |
|  2 |         1 |        1 |          1 |     120 | 2015-11-06 |
|  3 |         2 |        2 |          2 |     120 | 2015-11-04 |
|  4 |         2 |        2 |          1 |      60 | 2015-11-10 |
|  5 |         1 |        2 |          1 |      60 | 2015-11-10 |
|  6 |         2 |        2 |          1 |      40 | 2015-11-15 |
|  7 |         1 |        2 |          2 |      30 | 2015-11-15 |
+----+-----------+----------+------------+---------+------------+

I would like to display SUM of all lessons for each user for given month. The SUM should by grouped by each Places and Subjects.

The result in final PHP output should look like this:

November 2015                                             
+------------+-------------+---------------+-------------+
| Users.name | Places.name | Subjects.name | sum(length) |
+------------+-------------+---------------+-------------+
|   Burt Olm |           - |             - |           - |
|   Jane Doe |  Building B |          Math |         100 |
|          = |           = |       English |         120 |
|   John Doe |  Building A |          Math |         120 |
|          = |  Building B |          Math |          60 |
|          = |           = |       English |          30 |
+------------+-------------+---------------+-------------+

I have tried creating the full output in pure SQL query using multiple GROUP BY (Group by - multiple conditions - MySQL), but when I do GROUP BY User.id,Places.id it shows each user only once (3 results) no matter the other GROUP BY conditions.

SQL:

SELECT PastLectures.id_users,Users.name AS user,Places.name AS places,Subjects.name AS subjects
  FROM PastLectures
  LEFT JOIN Users ON PastLectures.id_users = Users.id
  LEFT JOIN Places ON PastLectures.id_Places = Places.id
  LEFT JOIN Subjects ON PastLectures.id_Subjects = Subjects.id
  WHERE date >= \''.$monthStart->format('Y-m-d H:i:s').'\' AND date <= \''.$monthEnd->format('Y-m-d H:i:s').'\'
  GROUP BY Users.id,Places.id
  ORDER BY Users.name,Places.name,Subjects.name

But I don't mind if part of the solution is done in PHP, I just don't know what to do next.

EDIT:

I also have a table Timetable, that stores who regularly teaches what and where. It stores only used combinations of the tables (each valid combination once).

Timetable = lectures that regularly take place
+----+-----------+----------+------------+-------------+
| id | id_users  | id_place | id_subjects| other_stuff |
+----+-----------+----------+------------+-------------+
|  1 |         1 |        1 |          1 |           x |
|  2 |         1 |        2 |          1 |           y |
|  3 |         1 |        2 |          2 |           z |
|  4 |         2 |        2 |          1 |           a |
|  5 |         2 |        2 |          2 |           b |
+----+-----------+----------+------------+-------------+

Is it possible to add only users with combinations that have a row in this table?

In this case it would mean omitting Burt Olm (no id=3 in Timetable). But if Burt has a Timetable entry and still no PastLectures entry, he would show here as in sample result (he should have had a lecture that month, because he is in Timetable, but no lectures took place).

Based on @Barmar's solution I updated the final SQL by making Timetable a primary table and adding one more LEFT JOIN to suffice those needs.

Final SQL:

SELECT Users.name AS user,Places.name AS places,Subjects.name AS subjects, SUM(PastLectures.length)
  FROM Timetable
  LEFT JOIN PastLectures ON PastLectures.id_users = Timetable.id_users AND PastLectures.id_place = Timetable.id_place AND PastLectures.id_subjects = Timetable.id_subjects
    AND date BETWEEN '2015-11-01 00:00:00' AND '2015-11-30 23:59:59'
  LEFT JOIN Places ON Timetable.id_Place = Places.id
  LEFT JOIN Subjects ON Timetable.id_Subjects = Subjects.id
  LEFT JOIN Users ON Timetable.id_users = Users.id
  GROUP BY Timetable.id,Timetable.id_users,Timetable.id_Place,Timetable.id_Subjects
  ORDER BY Users.name,Places.name,Subjects.name

Upvotes: 2

Views: 1589

Answers (2)

trincot
trincot

Reputation: 350107

According to standard SQL, you should GROUP BY all the fields you select, except for the aggregated fields (like sum). Althought MySql allows to do otherwise, when it can be done adhering to the standards, it is better to do so (who knows when you need to port your code to another database engine). So write your SQL like this:

  SELECT    PastLectures.id_users,
            Users.name AS user,
            Places.name AS places,
            Subjects.name AS subjects,
            Sum(length)
  FROM      PastLectures
  LEFT JOIN Users       ON PastLectures.id_users = Users.id
  LEFT JOIN Places      ON PastLectures.id_Places = Places.id
  LEFT JOIN Subjects    ON PastLectures.id_Subjects = Subjects.id
  WHERE     date BETWEEN \''.$monthStart->format('Y-m-d H:i:s').'\'
                 AND     \''.$monthEnd->format('Y-m-d H:i:s').'\'
  GROUP BY  PastLectures.id_users,
            Users.name,
            Places.name,
            Subjects.name
  ORDER BY  Users.name,
            Places.name,
            Subjects.name

Upvotes: 0

Barmar
Barmar

Reputation: 780724

You need to include Subjects.id in the GROUP BY, so you get a separate result for each subject.

Also, you shouldn't use columns in tables that are joined with LEFT JOIN in the GROUP BY column. If you do that, all the non-matching rows will be grouped together, because they all have NULL in that column. Use the columns in the main table.

GROUP BY PastLectures.id_users, PastLectures.id_Place, PastLectures.id_Subjects

DEMO

Note that there's no row for Burt Olm in the demo output, because all his rows are filtered out by the WHERE clause. If you want all users to be shown, you should make Users the main table, not PastLectures. And the date criteria needs to be moved into the ON clause when joining with PastLectures.

SELECT Users.name AS user,Places.name AS places,Subjects.name AS subjects, SUM(length)
  FROM Users
  LEFT JOIN PastLectures ON PastLectures.id_users = Users.id 
    AND date BETWEEN '2015-11-01 00:00:00' AND '2015-11-30 23:59:59'
  LEFT JOIN Places ON PastLectures.id_Place = Places.id
  LEFT JOIN Subjects ON PastLectures.id_Subjects = Subjects.id
  GROUP BY Users.id, PastLectures.id_Place, PastLectures.id_Subjects
  ORDER BY Users.name,Places.name,Subjects.name

DEMO

Upvotes: 4

Related Questions