Multiple Rows with Null Values Returned from CASE WHEN - Single Row?

Question

I'm using a CASE WHEN statement to return different products' names and prices during certain periods from my dataset, but it is returning numerous rows per unique product. Is this an incorrect use of CASE WHEN, and if so, how would I fix this?

SELECT product_name,
    CASE WHEN period_fiscal_year=1 THEN product_price ELSE 0 END as Period_1,
    CASE WHEN period_fiscal_year=2 THEN product_price ELSE 0 END as Period_2,
    CASE WHEN period_fiscal_year=3 THEN product_price ELSE 0 END as Period_3
FROM product_table

The result should ideally look like this (one example):

|product_name|Period_1|Period_2|Period_3|
|1st Product |   $25  |   $35  |   $20  |

Currently, it looks like:

|product_name|Period_1|Period_2|Period_3|
|1st Product |   $25  |    0   |    0   |
|1st Product |    0   |   $35  |    0   |
|1st Product |    0   |    0   |   $20  |
|1st Product |    0   |    0   |    0   |

How would I go about consolidating these rows and thus removing the 0 values? Thank you!

score 2 · Answer 1 · answered Dec 06 '17 at 23:04

It seems that you have more than one entry for 1st Product in the table which is why you would need to aggregate the prices for different periods at the product_name level.

SELECT product_name,
SUM(CASE WHEN period_fiscal_year=1 THEN product_price ELSE 0 END) as Period_1,
SUM(CASE WHEN period_fiscal_year=2 THEN product_price ELSE 0 END) as Period_2,
SUM(CASE WHEN period_fiscal_year=3 THEN product_price ELSE 0 END) as Period_3
FROM product_table
GROUP BY product_name;

Multiple Rows with Null Values Returned from CASE WHEN - Single Row?

1 Answers1