CODEWITHSUNDEEP
sql-server
Simos Fasouliotis
Simos Fasouliotis

Reputation: 1390

SUM values when conditions met

I have the following SQL Server table with the following data:

PRICE(decimal 9,2)   PRICETYPE(int)     EVENTDETAILID (int)

PRICE          PRICETYPE          EVENTDETAILID
------------------------------------------------
99                 1                    1
99                 1                    1
99                 1                    1
15                 0                    1
15                 0                    1
50                 1                    2
50                 1                    2

I want to SUM the PRICE of the results with the following conditions:

for each EVENTDETAILID, SUM every line with a pricetype is 0 and for every line per EVENTDETAILID pricetype is 1, then add it only 1 time.

For the above example the required output would be:

I've tried the following but doesn't work as expected as I'm not able to add PRICE only once if PRICETYPE is 1:

SELECT 
    SUM(PRICE) 
FROM 
    ReservationDetails 
GROUP BY 
    eventDetail_id

Upvotes: 0

Views: 47

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

You may phrase this as an aggregation (GROUP BY) query over the event detail ID. The sum can be broken into a conditional sum when the detail ID is 0, combined with the maximum price when the detail ID is 1. Since you told us that the price is always the same when PRICETYPE=1, therefore we can choose any single value.

SELECT
    EVENTDETAILID,
    SUM(CASE WHEN PRICETYPE = 0 THEN PRICE ELSE 0 END) +
    MAX(CASE WHEN PRICETYPE = 1 THEN PRICE ELSE 0 END) AS total
FROM yourTable
GROUP BY
    EVENTDETAILID
ORDER BY
    EVENTDETAILID;

enter image description here

Demo

Upvotes: 3

Related Questions