SQL grouping: Creating monthly columns with grouped data

Question

I have the following mySQL query which generates a table listing the individual failure data and groups by the month and frequency of different types of failures.

SELECT (CASE WHEN MONTH(runtable.time) = 1 THEN 'Jan'
WHEN MONTH(runtable.time) = 2 THEN 'FEB'
WHEN MONTH(runtable.time) = 3 THEN 'MAR'
WHEN MONTH(runtable.time) = 4 THEN 'APR'
WHEN MONTH(runtable.time) = 5 THEN 'MAY'
WHEN MONTH(runtable.time) = 6 THEN 'JUN'
WHEN MONTH(runtable.time) = 7 THEN 'JUL'
WHEN MONTH(runtable.time) = 8 THEN 'AUG'
WHEN MONTH(runtable.time) = 9 THEN 'SEP'
WHEN MONTH(runtable.time) = 10 THEN 'OCT'
WHEN MONTH(runtable.time) = 11 THEN 'NOV'
WHEN MONTH(runtable.time) = 12 THEN 'DEC'
ELSE 'Error' END) as date_month, runtable.operation, pareto.failure
FROM runtable
JOIN pareto ON pareto.run_id = runtable.id
WHERE runtable.time BETWEEN '2016-01-01 00:00:00' AND '2016-12-13 23:59:59'     
AND runtable.operation IN ('d','hr')
GROUP BY MONTH(runtable.time), pareto.failure
ORDER BY MONTH(runtable.time) desc, n desc;

I would like to be able to have the months as separate columns in in the table along with the total for the year, but am unaware of how to do this. My current issue is the only way I have tried of doing this involves using sum or case for each month but then I am unable to group the count of different failure types together for each month.

See http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Dec 13 '16 at 14:34

score 0 · Answer 1 · edited May 23 '17 at 12:08

0

The way is exactly what you described: you need to use CASE WHEN and SUM functions to solve this. Take a look at this thread:

how to select 2 table like this

By the way, you have easier ways to describe months without a case function as your example code. Take a look:

MySQL DATE_FORMAT() Function

Something like: select DATE_FORMAT(now(),'%m')

I didn't understand your problem with the error column. Be specify what problems are you run into.

Good luck!

edited May 23 '17 at 12:08

Community

1
1

answered Dec 13 '16 at 14:41

jfneis

2,139
18
31

The "how to select 2 table link" does not seem to be working. – Themtbboat Dec 13 '16 at 14:45
Also the problem encountered is that I have tried using SUM for each month and then counting the frequency of failures but I am then trying to group the individual failure types together at the end of the query which is invalid. – Themtbboat Dec 13 '16 at 14:47
Check again, I fix the link – Juan Carlos Oropeza Dec 13 '16 at 14:48
@Themtbboat you can't SUM and group individual failures on the same query. One option is to group it using columns instead of lines, as you will do for months (with CASE WHEN). – jfneis Dec 13 '16 at 15:21

McNets · Answer 2 · 2016-12-13T15:50:00.047

As far as I understand you're looking for the sum(pareto.failues) by month of each operation.

Check it here: http://rextester.com/MTDK27603

SELECT 
    runtable.operation,
    pareto.failure,
    COUNT(CASE WHEN MONTH(runtable.time) = 1 THEN 1 else NULL end) as 'Jan',
    COUNT(CASE WHEN MONTH(runtable.time) = 2 THEN 1 else NULL end) as 'Feb',
    COUNT(CASE WHEN MONTH(runtable.time) = 3 THEN 1 else NULL end) as 'Mar',
    COUNT(CASE WHEN MONTH(runtable.time) = 4 THEN 1 else NULL end) as 'Apr',
    COUNT(CASE WHEN MONTH(runtable.time) = 5 THEN 1 else NULL end) as 'May',
    COUNT(CASE WHEN MONTH(runtable.time) = 6 THEN 1 else NULL end) as 'Jun',
    COUNT(CASE WHEN MONTH(runtable.time) = 7 THEN 1 else NULL end) as 'Jul',
    COUNT(CASE WHEN MONTH(runtable.time) = 8 THEN 1 else NULL end) as 'Aug',
    COUNT(CASE WHEN MONTH(runtable.time) = 9 THEN 1 else NULL end) as 'Sep',
    COUNT(CASE WHEN MONTH(runtable.time) = 10 THEN 1 else NULL end) as 'Oct',
    COUNT(CASE WHEN MONTH(runtable.time) = 11 THEN 1 else NULL end) as 'Nov',
    COUNT(CASE WHEN MONTH(runtable.time) = 12 THEN 1 else NULL end) as 'Dec',
    COUNT(pareto.failure) as 'Total year'
FROM 
    runtable
    JOIN pareto 
        ON pareto.run_id = runtable.id
WHERE
    runtable.time BETWEEN '2016-01-01 00:00:00' AND '2016-12-13 23:59:59'     
    AND runtable.operation IN ('d','hr')
GROUP BY 
    runtable.operation, pareto.failure
;

Thanks for you response. However I require the sum of each of the failures per month not just the sum of the total pareto.failures. — Themtbboat, Dec 13 '16 at 15:04
| Failure | Jan | Feb | Total |:-----------|------------:|:------------:| | x | 2 | 4 | 10 |y | 5 | 7 | 12 | z | 1 | 2| 5 — Themtbboat, Dec 14 '16 at 09:48

SQL grouping: Creating monthly columns with grouped data

2 Answers2