CODEWITHSUNDEEP
mysql
john tully
john tully

Reputation: 309

SUM values of CASE in Mysql GROUP BY

I am not sure this is possible but below is what I am trying to achieve.

$sql = "SELECT CASE WHEN products.type=1 THEN 50 WHEN products.type=2 THEN 25 ELSE 0 END 'PROD_NUM', CASE WHEN categories.id=4 THEN 12 WHEN categories.id=7 THEN 40 ELSE 5 END 'CAT_NUM', ('PROD_NUM'+'CAT_NUM') 'TOTAL_NUM' FROM products JOIN categories..... GROUP BY products.color....

This is of course just a mock example but the question is it possible to sum the values of the two CASE? Thanks

Upvotes: 1

Views: 300

Answers (2)

raina77ow
raina77ow

Reputation: 106443

If you don't need the aggregation (and it seems from the OP you don't), you can actually use the names of the 'computed columns' in SELECT expression:

SELECT 
  CASE products.type
    WHEN 1 THEN 50 
    WHEN 2 THEN 25 
    ELSE 0 END `PROD_NUM`, 
  CASE categories.id
    WHEN 4 THEN 12 
    WHEN 7 THEN 40 
    ELSE 5 END `CAT_NUM`, 
  (SELECT PROD_NUM) + (SELECT CAT_NUM) `TOTAL_NUM` 
FROM products

Here's the sample DBFiddle (works on MySQL 5.7). This approach won't handle the columns computed with aggregate functions though.

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521914

It looks like you want conditional aggregation here:

SELECT
    p.color,
    SUM(CASE p.type WHEN 1 THEN 50 WHEN 2 THEN 25 ELSE 0 END) AS PROD_NUM,
    SUM(CASE c.id WHEN 4 THEN 12 WHEN 7 THEN 40 ELSE 5 END) AS CAT_NUM
    SUM(CASE p.type WHEN 1 THEN 50 WHEN 2 THEN 25 ELSE 0 END) +
      SUM(CASE c.id WHEN 4 THEN 12 WHEN 7 THEN 40 ELSE 5 END) AS TOTAL_NUM
FROM products p
INNER JOIN categories c
-- ON etc.
GROUP BY p.color;

Note that you'll have to repeat both CASE expressions if you want to sum them together. The only way around this would be to subquery your current query, and then add the PROD_NUM and CAT_NUM aliases. But, given that the CASE expressions are fairly small, I vote for the above version.

Upvotes: 2

Related Questions