SUM ( CASE WHEN A=B THEN COUNT*X) statment

Question

I am trying to run a query in SQLite3 where I want the grand total of Savings column from Case statement. My SQL query looks like this now:

SELECT COUNT(PhoneNumber), Dept, Device
    CASE
    WHEN Device='Apple' THEN 500*COUNT(PhoneNumber)
    WHEN Device='Samsung' THEN 400*COUNT(PhoneNumber)
    ELSE 100*COUNT(PhoneNumber)
    END AS 'Savings'
FROM TestTable
WHERE Active= 'No' OR Cancel=1
GROUP BY Dept, Device

I tried inserting SUM() in front of CASE statement but I am getting an error "Result: misuse of aggregate function count() At line 1:" Could anyone guide me on how to build this query?

I am confused. Does the query you are showing work or not? Can you please show sample data and the expected result, so we see what "the grand total of Savings column" is supposed to look like? — Thorsten Kettner, Apr 08 '20 at 05:38
`AS 'Savings'` is invalid SQL by the way. The standard delimiter for alias names is the double quotes (`AS "Savings"). While SQLIte lets this slip, I'd recommend never to use single quotes for names, as this can lead to faulty results in other situations. — Thorsten Kettner, Apr 08 '20 at 05:49

score 2 · Answer 1 · answered Apr 08 '20 at 05:33

This query looks valid to me. You group by device (and department) and count phone numbers. Then, if your grouped row is on Apple, you display the count multiplied with 500; when it's a Samsung group row, you display you show it multiplied by 400; and else multiplied by hundred.

Maybe the DBMS has problems to see that it can first aggregate and then evaluate CASE WHEN. Here is an alternative way of writing the same thing. Please try it to see whether this works.

SELECT Dept, Device,
  SUM(
    CASE WHEN PhoneNumber IS NOT NULL AND Device = 'Apple' THEN 500
         WHEN PhoneNumber IS NOT NULL AND Device = 'Samsung' THEN 400
         WHEN PhoneNumber IS NOT NULL THEN 100
         ELSE 0
    END
  ) AS Savings
FROM TestTable
WHERE Active = 'No' OR Cancel = 1
GROUP BY Dept, Device
ORDER BY Dept, Device;

SUM ( CASE WHEN A=B THEN COUNT*X) statment

1 Answers1