CODEWITHSUNDEEP
mysqlsql
Cracktastic
Cracktastic

Reputation: 45

Group count results by month

I am trying to obtain types of attendances per year/month in order to populate a bar chart. These are my two tables ( I know the english is a bit off ):

**presences**
ID | Id_student | Id_class | Presence
3        2           27         1
4        4           28         2
...     ...         ...        ...

**class**
ID | Id_schedule | Date_class | State
27        6        2017-12-11     1
28        7        2018-01-13     1
...      ...          ...        ...

This is my current queries, which returns ALL results instead of the intended result, which is just the current year:

   SELECT
        DATE_FORMAT(Date_class, '%Y') as 'year', DATE_FORMAT(Date_class, '%M') as 'month',
        (SELECT COUNT(distinct ID) FROM presences WHERE Presence = 0) as Faltas,
        (SELECT COUNT(distinct ID) FROM presences WHERE Presence = 1) as Pre,
        (SELECT COUNT(distinct ID) FROM presences WHERE Presence = 2) as Temp,
        (SELECT COUNT(distinct ID) FROM presences WHERE Presence = 3) as Canc,
        (SELECT COUNT(distinct ID) FROM presences WHERE Presence = 4) as FaltasTemp
    FROM class, presences
    WHERE YEAR(Date_class) = YEAR(CURDATE())
    AND
    presences.Id_class = class.ID
    GROUP BY DATE_FORMAT(Date_class, '%Y%m')

This is the result, which is also displaying occurrences from last year:

year  month  Faltas Pre Temp Canc FaltasTemp
2018 January   2     6   5    0       1

Could anyone help me identify what is missing?

Upvotes: 1

Views: 53

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I think you want a much simpler query:

   SELECT YEAR(c.Date_class) as year, MONTH(c.Date_class) as month,
          SUM(p.Presence = 0) as Faltas,
          SUM(p.Presence = 1) as Pre,
          SUM(p.Presence = 2) as Temp,
          SUM(p.Presence = 3) as Canc,
          SUM(p.Presence = 4) as FaltasTemp
    FROM class c JOIN
         presences p
         ON p.Id_class = c.ID
    WHERE YEAR(c.Date_class) = YEAR(CURDATE())
    GROUP BY YEAR(c.Date_class), MONTH(c.Date_class);

Notes:

  • Use proper, explicit JOIN syntax. Never use commas in the FROM clause.
  • The tables have aliases, which make the queries easier to write and to read.
  • The columns are all qualified (with the table name) so you know where they come from.
  • You don't need a separate subquery for each column.

Upvotes: 2

Related Questions