CODEWITHSUNDEEP
mysqlsql
Arun Shyam
Arun Shyam

Reputation: 571

Case When with union between cases

Suppose I have a SQL statement that looks like this:

select supplier, case when platform in (5,6) then 'mobile' 
               when platform in (7,8) then 'tablet' end as platform,
       count(*) as clicks
from supplier_table
group by supplier, platform;

This gives me the breakdown of clicks received by each supplier by different platforms example : [("ABC",mobile, 200),("ABC", tablet, 300)] for supplier "ABC"

My aim is the do this plus find the sum of both the clicks and put it in a platform named lets say 'all'.

I tried doing that with the same SQL statement by adding an extra case.

select supplier, case when platform in (5,6) then 'mobile' 
        when platform in (7,8) then 'tablet' when platform in (5,6,7,8) then 'all' 
       end as platform,
       count(*) as clicks
from supplier_table
group by supplier, platform;

But this does not work and gives me the same result as above without the 'all' platform. Is it at possible at all by using Case When statement or do I need to do an upper level aggregation by selecting over the original SQL results?

Thanks.

Update:

It is MYsql (RDBMS) and yes I need a separate row with value 'all' (this would contain sum of clicks of both 'mobile' and 'tablet'.

eg: example : [("ABC",mobile, 200),("ABC", tablet, 300), ("ABC", all, 500)]

Upvotes: 1

Views: 104

Answers (2)

EkriirkE
EkriirkE

Reputation: 2304

If you want results like 

supplier platform clicks
abc      tablet   12
abc      mobile   34

Add your case statement to a GROUP BY

select supplier, case when platform in (5,6) then 'mobile' 
               when platform in (7,8) then 'tablet' end as platform,
       count(*) as clicks
from supplier_table
group by supplier,case when platform in (5,6) then 'mobile' 
               when platform in (7,8) then 'tablet' end

alternatively if you want results like this:

supplier tablet_clicks mobile_clicks total_clicks
abc      12            34            46

You could split your cases inside sums of 1 acting like a conditional count something like this:

select supplier, sum(case when platform in (5,6) then 1 end) as mobile_clicks,
               sum(case when platform in (7,8) then 1 end) as tablet_clicks,
               count(1) total_clicks
from supplier_table
group by supplier

And after seeing your update, If you want results like 

supplier platform clicks total_clicks
abc      tablet   12     46
abc      mobile   34     46

You need to use a subquery like so

select supplier, case when platform in (5,6) then 'mobile' 
               when platform in (7,8) then 'tablet' end as platform,
       count(*) as clicks,
       (select count(1) from supplier_table as x where x.supplier=supplier_table.supplier where x.platform in (5,6,7,8)) as total_clicks
from supplier_table
group by supplier,case when platform in (5,6) then 'mobile' 
               when platform in (7,8) then 'tablet' end

If you want results like 

supplier platform clicks
abc      tablet   12
abc      mobile   34
abc      all      46

you do use a UNION as suspected with a separate non-segregating query

select supplier, case when platform in (5,6) then 'mobile' 
               when platform in (7,8) then 'tablet' end as platform,
       count(*) as clicks
from supplier_table
group by supplier,case when platform in (5,6) then 'mobile' 
               when platform in (7,8) then 'tablet' end
UNION ALL
select supplier,'all',count(1) from supplier_table 
where platform in (5,6,7,8)
group by supplier

Upvotes: 4

Hart CO
Hart CO

Reputation: 34784

The problem is that your last WHEN criteria will never be met because one of the prior two will always be met first. You need either a separate CASE statement, or perhaps a conditional SUM(), something like:

SUM(CASE WHEN platform in (5,6,7,8) THEN 1 END)

Also, you should have any non-aggregate values, including CASE statements in a GROUP BY clause, even if mySQL doesn't error out on incomplete GROUP BY clauses.

Upvotes: 0

Related Questions