Show records grouped by month in a MYSQL query

Question

Show records grouped by month in a MYSQL query

Navigation

#1 by (1 votes)

1

I have the whole system created, only the reports are missing, the main table is averias which has as attributes campo , fecha and other irrelevant, the SQL I have so far is as follows:

SELECT centro, MONTH(fecha) as mes, COUNT(*) as Total FROM averias WHERE 
fecha BETWEEN '2018-01-01' AND '2018-12-31' GROUP BY centro, mes

this gives me back the following:

centro | mes | Total
---------------------
2003   |5    |1 
2005   |5    |2 
2019   |5    |1 
2020   |5    |2 
2026   |5    |1 
2030   |5    |1 
2055   |5    |1 
2188   |5    |1

My problem occurs when I take this to a report in the view to do the breakdown by month, I have no idea how to do it.

I need the report to be as follows:

Centro  Ene Feb Mar Abr May Jun Jul Ago Sep Oct Nov Dic 
2035     1   5   6   2   3   4   2   2   2   3   4   5  
2038     4   5   4   2   3   4   2   1   2   3   4   5

and then I can visualize it in my System that is in Codeigniter.

Greetings and thanks in advance.

mysql php codeigniter

asked by elyera 29.05.2018 в 03:20

source

1 answer

Error entering a DateTime data in Visual Studio C # Integrals defined in python - Area under standard normal curve

score 1 · Accepted Answer

To make the query you can nest the months as follows:

select V.campo, (
    select count(*)
    from averias A 
    where A.fecha BETWEEN '2018-01-01' AND '2018-01-31' and A.campo=V.campo
) as ene,(
    select count(*)
    from averias A 
    where A.fecha BETWEEN '2018-02-01' AND '2018-02-31' and A.campo=V.campo
) as feb,(
    select count(*)
    from averias A 
    where A.fecha BETWEEN '2018-03-01' AND '2018-03-31' and A.campo=V.campo
) as mar,(
    select count(*)
    from averias A 
    where A.fecha BETWEEN '2018-04-01' AND '2018-04-31' and A.campo=V.campo
) as abr,(
    select count(*)
    from averias A 
    where A.fecha BETWEEN '2018-05-01' AND '2018-05-31' and A.campo=V.campo
) as may,(
    select count(*)
    from averias A 
    where A.fecha BETWEEN '2018-06-01' AND '2018-06-31' and A.campo=V.campo
) as jun,(
    select count(*)
    from averias A 
    where A.fecha BETWEEN '2018-07-01' AND '2018-07-31' and A.campo=V.campo
) as jul,(
    select count(*)
    from visita A 
    where A.fecha BETWEEN '2018-08-01' AND '2018-08-31' and A.campo=V.campo
) as ago,(
    select count(*)
    from averias A 
    where A.fecha BETWEEN '2018-09-01' AND '2018-09-31' and A.campo=V.campo
) as sep,(
    select count(*)
    from averias A 
    where A.fecha BETWEEN '2018-10-01' AND '2018-10-31' and A.campo=V.campo
) as oct,(
    select count(*)
    from averias A 
    where A.fecha BETWEEN '2018-11-01' AND '2018-11-31' and A.campo=V.campo
) as nov,(
    select count(*)
    from averias A 
    where A.fecha BETWEEN '2018-12-01' AND '2018-12-31' and A.campo=V.campo
) as dic
from (select DISTINCT campo
from averias) V

It may seem extensive but it's just like you do the queries per month and the unieras in one, that way you can show the records per month and imagine that you will change the year with php.

Another solution is to use case in the following way:

SELECT averias.centro, 
SUM(CASE WHEN MONTH(averias.fecha) = 1 THEN 1 ELSE 0 END) AS Ene,
SUM(CASE WHEN MONTH(averias.fecha) = 2 THEN 1 ELSE 0 END) AS Feb,
SUM(CASE WHEN MONTH(averias.fecha) = 3 THEN 1 ELSE 0 END) AS Mar,
SUM(CASE WHEN MONTH(averias.fecha) = 4 THEN 1 ELSE 0 END) AS Abr,
SUM(CASE WHEN MONTH(averias.fecha) = 5 THEN 1 ELSE 0 END) AS May,
SUM(CASE WHEN MONTH(averias.fecha) = 6 THEN 1 ELSE 0 END) AS Jun,
SUM(CASE WHEN MONTH(averias.fecha) = 7 THEN 1 ELSE 0 END) AS Jul,
SUM(CASE WHEN MONTH(averias.fecha) = 8 THEN 1 ELSE 0 END) AS Ago,
SUM(CASE WHEN MONTH(averias.fecha) = 9 THEN 1 ELSE 0 END) AS Sep, 
SUM(CASE WHEN MONTH(averias.fecha) = 10 THEN 1 ELSE 0 END) AS Oct,
SUM(CASE WHEN MONTH(averias.fecha) = 11 THEN 1 ELSE 0 END) AS Nov,
SUM(CASE WHEN MONTH(averias.fecha) = 12 THEN 1 ELSE 0 END) AS Dic
FROM averias
WHERE averias.fecha BETWEEN '2018-01-01' AND '2018-12-31'
GROUP BY averias.centro

I hope it serves those who need it. Greetings.