CODEWITHSUNDEEP
sql
Frenchi In LA
Frenchi In LA

Reputation: 3169

Special Group by & SUM

I have a table with the following fields: ID, S, W and data Like:

1,s1,w1
1,s2,w1
1,s3,w2
2,s4,w2

I would like to calculate SUM of S for each Id only for W = w1 and zero as result for other ID that don't have any data for w1. for this example I have to get 

1, s1+s2, w1
2, 0, null or 0

which means if Id has w1 show only this some,but if Id=2 has only W different of w1 show it as well. I tried

SUM(CASE W = w1 THEN S ELSE 0 END) AS S GROUP BY ID, W but don't how to show also W? I hope i was clear is part of a UDF with a lot of other tables involved. Any help would be greatly appreciated.

Upvotes: 0

Views: 45

Answers (3)

Mehmet Balioglu
Mehmet Balioglu

Reputation: 2302

Does the following work for you?

Select id, SUM(CASE W = w1 THEN S ELSE 0 END) over(Partition by id order by 1) total, W from yourtable

Upvotes: 1

Lamak
Lamak

Reputation: 70658

If I understood you correctly, you want:

SELECT  ID,
        SUM(CASE WHEN W = 'w1' THEN S ELSE 0 END) AS S,
        CASE WHEN W = 'w1' THEN W ELSE 0 END AS W
FROM YourTable
GROUP BY ID,
         CASE WHEN W = 'w1' THEN W ELSE 0 END

Upvotes: 1

Nicolas
Nicolas

Reputation: 351

Can"t you have a second Case statement ? Like CASE WHEN W = w1 THEN W ELSE 0 END as W ?

Upvotes: 1

Related Questions