How to use sum (case ... when ... then ...) properly?

Question

I want to sum rows with a specific value as a particular column. Earlier I did something like this:

SELECT date                                          AS 'Date',
   sum(CASE license_id WHEN 'a' THEN data.Amount ELSE 0 END) AS 'a',
   sum(CASE license_id WHEN 'b' THEN data.Amount ELSE 0 END) AS 'b',
   sum(
           CASE license_id WHEN '1' THEN data.Amount ELSE 0 END +
           CASE license_id WHEN '2' THEN data.Amount ELSE 0 END +
           CASE license_id WHEN '3' THEN data.Amount ELSE 0 END
       )                                                        AS 'c',
   sum(
           CASE license_id WHEN '10' THEN data.Amount ELSE 0 END +
           CASE license_id WHEN '11' THEN data.Amount ELSE 0 END +
           CASE license_id WHEN '12' THEN data.Amount ELSE 0 END
       )                                                        AS 'd'
FROM ...
...

This does exactly what I want but now I have a new scenario. I need to sum up different temp results. The following code does NOT WORK but just to explain what I want to have:

SELECT date                                          AS 'Date',
   sum(CASE license_id WHEN 'b' THEN data.Amount ELSE 0 END) AS 'b',
   sum(    CASE license_id WHEN '1' THEN data.Amount ELSE 0 END +
           CASE license_id WHEN '2' THEN data.Amount ELSE 0 END +
           CASE license_id WHEN '3' THEN data.Amount ELSE 0 END +
           b
       )                                                        AS 'c',
FROM ...
...

So my questions are since this is only a part of the whole SELECT-Statement:

• Can I somehow make that CASE ... WHEN ... THEN ... + CASE ... WHEN ... THEN ... + CASE ... WHEN ... THEN ... simpler?
• Is there any possibility to achieve that I can use the temporary results in further calculations?

Thanks for your help in advance!

Answer 1

The problem is that b is not understood -- or at best a reference to a column in the table rather than your expression.

This is a general property of SQL. You cannot re-use a column alias in the SELECT, FROM, or WHERE clauses in the same query. You have basically three options in MySQL:

• Use a subquery
• Repeat the expression
• Use a CTE

In this case, there is actually a fourth option, because rewriting the query simplifies the logic:

select date,
       sum(case license_id when 'b' then data.Amount else 0 end) AS b,
       sum(case when license_id in ('b', '1', '2', '3')  then data.Amount else 0 
           end) as c,
from ...

Notice that the form of the case is a little different, where a boolean conditions comes after the when.

Also, single quotes are not needed for column aliases. If you want to prevent problems in your code, only use single quotes for string and date constants.