MySQL How to group by date with CASE statements as columns instead of rows

Question

Not sure if I'm even asking this correctly. I have a query that I am trying to display totals per each reason_id based on another tables qty value, but then group by months in the table where the reason_id exists.

My Query so far is:

select
month(f.c_date),
case when q.reason_id = 2 then sum(f.produced) else 0 end as 'LowM',
case when q.reason_id = 3 then sum(f.produced) else 0 end as 'LowP',
case when q.reason_id = 4 then sum(f.produced) else 0 end as 'LowSC',

from freeze f
inner join freezeq q on f.id = q.frz_id
inner join location l on f.t_id = l.id

where 1=1
and f.c_date like '%2018%'
group by q.reason_id, month(f.c_date);

I need to remove the "group by q.reason_id" but then of course the SUM values aren't separated.

I am trying to display the CASEs as columns and the SUM of the f.produced values, but group on the f.c_date.

I tried with sub queries a couple different ways, but can't seem to get it.

I appreciate any help the collective mind can give me!

Thanks!

score 0 · Accepted Answer · answered Aug 27 '18 at 21:11

You seem to want to use condition aggregate function, use case when in the sum function.

select
    month(f.c_date),
    sum(case when q.reason_id = 2 then f.produced) else 0 end) as 'LowM',
    sum(case when q.reason_id = 3 then f.produced else 0 end) as 'LowP',
    sum(case when q.reason_id = 4 then f.produced else 0 end) as 'LowSC'
from freeze f
inner join freezeq q on f.id = q.frz_id
inner join location l on f.t_id = l.id
where  f.c_date like '%2018%'
group by  month(f.c_date);

NOTE

I remove the 1=1 and because that no sence in your query.

Thank you very much for the info! That 100% helped. Also, thank you for helping me understand the correct terminology to use here. Helps me a lot! That where 1=1 is a bad habit of mine, then everything else is an AND for me. — Casy Choate, Aug 27 '18 at 21:22

spencer7593 · Answer 2 · 2018-08-27T21:31:40.517

I think you are asking about how to do conditional aggregation.

Move the aggregate to the outside of the condition. The conditional will return the scalar we want to aggregate (if the condition is satisfied) or return 0 or NULL (if the condition isn't satisfied). The the aggregate function will operate on the returned scalars.

Replace this:

  case when q.reason_id = 2 then sum(f.produced) else 0 end as 'LowM'

With this:

  SUM( CASE WHEN q.reason_id = 2 THEN f.produced ELSE 0 END ) AS `LowM`

Other notes:

If c_date is DATE, DATETIME or TIMESTAMP datatype,

replace this condition:

    f.c_date LIKE '%2018%'

with

    f.c_date >= '2018-01-01'
AND f.c_date  < '2018-01-01' + INTERVAL 1 YEAR

This will enable MySQL to make use of a range scan operation on a suitable index (if a suitable index is available.)

Using a LIKE comparison on a DATE will force MySQL to evaluate every row in the table, to convert every c_date value into a string, and doing the string comparison.

(We might like the optimizer to be "smart" enough to figure out the more efficient rewrite, but it just isn't that smart.)

The WHERE 1=1 doesn't have any impact on performance. We frequently see this in dynamically generated WHERE clauses; the application code can just append AND whatever, and not be cluttered up with a check to see if we need a WHERE keyword in place of the AND (for the first condition in the WHERE clause.)

Any newbies that are confused by the WHERE 1=1 can be brought up to speed very easily.

Thank you so much for this! Exactly what I was missing. Also thanks for giving me the proper terminology to use and understand better what I'm doing. — Casy Choate, Aug 27 '18 at 21:21
Ah! Great! Performance is always a plus. Thank you for that extra hint. I will do that — Casy Choate, Aug 27 '18 at 21:27

MySQL How to group by date with CASE statements as columns instead of rows

2 Answers2