1

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;
Improve this question
1
  • do you want to see it for all students or will you supply a student ID as a parameter? Commented Nov 21, 2013 at 18:39

2 Answers 2

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

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.
Just change the INNER JOIN to LEFT JOIN. It should give you what you want.
This works like a charm ! Thanks Filipe ! Long story short, what's the difference between inner and left ?
See here for a very good visual explanation :)
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
Improve this answer

1 Comment

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 ?