SELECT STATEMENT

[Kido]

Hi all,
I want to do a select statement and the scenario is as follows;

Table T has columns C1, C2, C3 and C4 with the following data:

C1 C2 C3 C4
A 10 23 T
B 25 25 T
C 11 04 T
B 06 25 T
C 13 24 X
A 24 35 T
A 07 25 X
C 10 45 T

I want to do a sum of C2 & C3 for items A, B & C, i.e
to do a select sum(C2) where the condition in C4 applies i.e either T or X.

I want to get a result similar to:

Item Sum(C2), Type T Sum(C2), Type X
A 34 07
B 31 00
C 21 13

and similarly for C3.
Any one with idea on how to go about?

 

[reevda]

Hi Kido,

If you use SQL Server, try this:
select c1, sum(isnull(case c4 when 'T' then c2 end,0) ) as C2_T, sum(isnull(case c4 when 'X' then c2 end,0) ) as C2_X,
sum(isnull(case c4 when 'T' then c3 end,0) ) as C3_T, sum(isnull(case c4 when 'X' then c3 end,0) ) as C3_X
from __test
group by c1

Result:
C1 C2_T C2_X C3_T C3_X
A 34 7 58 25
B 31 0 50 0
C 21 13 49 24

It's not very clean, but it works.