Two COUNT and a GROUP BY

Question

I'm stuck with this query and I would like to have a hand.

So I have two tables : Course and Absence.

The purpose of my query is to count, for a student, all his courses and absences for each months of the year.

With just the courses, I have this query :

SELECT DATE_FORMAT(c.date, "%M") AS month,
       COUNT(*) AS courses
FROM Course c
GROUP BY month
ORDER BY MONTH(c.date);

The result looks like this :

+-----------+--------+
| month     | courses|
+-----------+--------+
| January   |     24 |
| February  |     20 |
| March     |     20 |
| April     |     22 |
| May       |     23 |
| June      |     20 |
| July      |     23 |
| August    |     22 |
| September |     20 |
| October   |     23 |
| November  |     23 |
| December  |     21 |
+-----------+--------+

I would like to have a new column, exactly like the courses one, but for the absences.

My Absence columns are : id, user_id, course_id, reason, justified

So, is this possible to use the courses group by with an absence count ?

If yes, how ?

Thanks in advance,

EDIT :

I have now this query :

SELECT a.month, a.courses, b.absences
FROM (SELECT DATE_FORMAT(c.DATE, "%M") AS month,
             COUNT(*) AS courses
      FROM Course c
      GROUP BY month) a
INNER JOIN (SELECT DATE_FORMAT(c.date, "%M") AS month,
                   COUNT(*) AS absences
            FROM Absence a
            LEFT JOIN Course c
            ON a.course_id = c.id
            GROUP BY month) b
ON a.month = b.month;

And the result is :

+----------+---------+----------+
| month    | courses | absences |
+----------+---------+----------+
| November |      23 |        2 |
| October  |      23 |        1 |
+----------+---------+----------+

The numbers are good, but when there are no absences, the courses are not shown. Is there a way to have a 0 on the absences ?

EDIT 2 :

This is the working query :

SELECT a.month, a.courses, b.absences
FROM (SELECT DATE_FORMAT(c.DATE, "%M") AS month,
             COUNT(*) AS courses
      FROM Course c
      GROUP BY month) a
LEFT JOIN (SELECT DATE_FORMAT(c.date, "%M") AS month,
                   COUNT(*) AS absences
            FROM Absence a
            LEFT JOIN Course c
            ON a.course_id = c.id
            GROUP BY month) b
ON a.month = b.month;

do you want to see it for all students or will you supply a student ID as a parameter? — Daniel E., Nov 21 '13 at 18:39

Filipe Silva · Accepted Answer · 2013-11-21T19:37:47.453

1

Try this:

SELECT a.month, a.courses, b.absences
FROM (SELECT DATE_FORMAT(c.DATE, "%M") AS month,
             COUNT(*) AS courses
      FROM Course c
      GROUP BY month) a
LEFT JOIN (SELECT DATE_FORMAT(c.date, "%M") AS month,
                   COUNT(*) AS absences
            FROM Absence a
            LEFT JOIN Course c
            ON a.course_id = c.id
            GROUP BY month) b
ON a.month = b.month;

This does two subqueries, one to find the count of courses and one for absences. Then you join them by the month.

It should give you what you are looking for.

edited Nov 21 '13 at 19:37

answered Nov 21 '13 at 18:36

Filipe Silva

21,189
5
53
68

Thanks you. You were missing the fact that an Absence has no date, but is linked to a course. I added a left join. Please take a look at my edit. – Nov 21 '13 at 19:32
Just change the INNER JOIN to LEFT JOIN. It should give you what you want. – Filipe Silva Nov 21 '13 at 19:38
This works like a charm ! Thanks Filipe ! Long story short, what's the difference between inner and left ? – Nov 21 '13 at 19:40
1

See [here](http://stackoverflow.com/a/6188334/1385896) for a very good visual explanation :) – Filipe Silva Nov 21 '13 at 19:50

score 0 · Answer 2 · answered Nov 21 '13 at 18:36

0

You should be able to do this with inline views:

SELECT c.month, c.courses, a.absences
FROM  (
        SELECT DATE_FORMAT(date, "%M") AS month,
               COUNT(*) AS courses
        FROM Course
        GROUP BY month
      ) AS c
      JOIN  (
              SELECT DATE_FORMAT(date, "%M") AS month,
                     COUNT(*) AS absences
              FROM Absence
              GROUP BY month
            ) AS a
        ON a.month = c.month
ORDER BY c.month

answered Nov 21 '13 at 18:36

Michael L.

620
3
17

Thanks you for you answer. You and Filipe have exactly the same query. I addes a missing left join. Have an hint for my new issue ? – Nov 21 '13 at 19:38

Two COUNT and a GROUP BY

2 Answers2