CODEWITHSUNDEEP
sql
erik7970
erik7970

Reputation: 713

Aggregate results based on case statement

Need some assistance writing a query. Suppose I'm using a table sales shown here:

trans_id    item_number     sales 
   1            1             10
   1            11            5
   2            11            3
   2            13            4

I want my output to contain trans_id, total sales for the transaction and a Y/N flag that can be determined by this case statement: CASE WHEN item_number BETWEEN 1 AND 10 THEN 'Y' ELSE 'N' END.

However, I need the flag to be applied at the aggregate level where if there is at least 1 item with a 'Y' flag, then the whole transaction should include a 'Y' flag. If no items have a 'Y' flag, then the entire transaction should be flagged 'N'. The output should essentially look like:

trans_id    flag        sales
   1         'Y'          15
   2         'N'          7

The query below nearly gets me there, but I would have to use it as a subquery to get me the expected results. Is it possible to modify the CASE statement or use some other approach so that I can get the desired results within the same SELECT statement (i.e. not using the query below as a subquery)?

SELECT trans_id
  , CASE WHEN item_number BETWEEN 1 AND 10 THEN 'Y' ELSE 'N' END
  , SUM(sales)
FROM sales
GROUP BY trans_id, item_number

Upvotes: 1

Views: 50

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269663

You can use max():

select trans_id,
       max(case when item_number between 1 and 10 then 'Y' else 'N' end),
       sum(sales)
from sales
group by trans_id;

With 'Y' and 'N', MAX() acts as an aggregate-OR operation. MIN() would be an aggregate-AND -- that is,all the item numbers are in that range.

Upvotes: 2

Related Questions