Regarding Aggregate conditions .. [message #284422] |
Thu, 29 November 2007 11:02  |
frank.svs
Messages: 162 Registered: February 2006
|
Senior Member |
|
|
Hai frns small help,
I have a table called sample and i have the following requirement. i.e i need sum(credit) group by ssn no.
One special condition is as follows:
For each distinct ssn if "flag" has the same CX value,then out of all the records with the same CX value, the highest "credit" value is added to the sum for that "ssn" and the rest are ignored.
If while adding "credit" to the sum and if "credit" value is equal to zero then "sum" value is used for summing else "credit" value is used.
Can any one help me out in trying this logic. I have tried but i could'nt able embed the conditions inbetween the Sql statetment.
Here is the query is used
select * from sample
id ssn credit flag sem
1 101 0 C9 0
2 101 4 C9 3
3 101 4.5 C9 2
4 101 3.5 C1 1
5 102 4.2 C3 3
6 103 0 C1 2
select ssn,flag,sum(case credit when 0 then sem else credit end) as sum from sam2
group by ssn,flag
ssn flag sum_val
101 C1 3.5
103 C1 2.0
102 C3 4.2
101 C9 8.5
The above output is wrong one.
Expected output
101 4.5+3.5=8.0
102 4.2
103 2.0
Any help would be appreciated
Regards,
|
|
|
|
|
|