Mysql - Conditional Group By

Question

I am trying to use CASE and GROUP BY together to conditionally filter results only if they match the CASE criteria, and if they don't, return results as if there were no GROUP BY criteria specified.

Here's a simple model of what I have:

es.id | es.acct_num | p.id | p.name
1001  | 4306-0      | 1569 | The High School
1002  | 4306-0      | 1569 | The High School
665   | 5906-7      | 981  | Rec Center
783   | 5906-7      | 1221 | The Gym

and here's what I would like to see:

es.id | es.acct_num | p.id | p.name
1001  | 4306-0      | 1569 | The High School
1002  | 4306-0      | 1569 | The High School
0     | 5906-7      | 0    | MULTI-SITE

Since es.acct_num 5906-7 has more than 1 distinct associated p.id, I would like it grouped by the es.acct_num as 1 line item, then have the es.id, p.id represented by a '0', and have the p.name represented by the string 'MULTI-SITE'.

However, since es.acct_num 4306-0 only has exactly 1 distinct associated p.id, I would like these all returned as individual line items as if no grouping condition was applied.

The solution escapes me. How can I accomplish this?

Answer 1

You can do this with a conditional aggregations:

SELECT (case when count(*) > 1 then 0 else max(es.id) end) as esid,
       es.acct_num,
       (case when count(*) > 1 then 0 else max(p.id) end) as pid,
       (case when count(*) > 1 then 'MULTI-SITE' else p.name end) as name,
       COUNT(*) AS cnt
FROM es join
     p
     on . . . 
GROUP BY es.acct_num;

Don't be fooled by the max() expressions. There is only one row for that part of the case, so these simply return the value in that row.

EDIT:

I think you need to do this with a correlated subquery, join/aggregation, or by counting the values using variables. This would be much easier in almost any other database, using window functions. Here is the idea, which is a bit hard to express in detail without seeing the full query:

SELECT (case when esp.cnt > 1 then 0 else max(es.id) end) as esid,
       es.acct_num,
       (case when esp.cnt > 1 then 0 else max(p.id) end) as pid,
       (case when esp.cnt > 1 then 'MULTI-SITE' else p.name end) as name
FROM es join
     p
     on . . .  join
     (select es.acct_num, count(distinct p.id) as cnt
      from . . .
      group by es.acct_num
     ) esp
     on esp.acct_num = es.acct_num
GROUP BY es.acct_num, (case when cnt = 0 then es.id end);