Regarding Aggregate conditions ..

Hai frns small help needed.

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,SELECT ssn
, SUM(max_credit)
FROM --MAX credit\ sem per ssn & flag
(SELECT dbo.my_table.ssn
, max_credit = MAX(CASE WHEN credit = 0 THEN sem ELSE credit END)
FROM dbo.my_table
GROUP BY dbo.my_table.ssn
, dbo.my_table.flag) AS mc
GROUP BY ssn|||Actually I think that is flawed. I don't think your sample data is comprehensive enough.|||Thanks, Here is more sample data

101 0 C9 0
101 4 C9 3
101 4.5 C9 2
101 0 C9 2
101 3.5 C1 1
101 3.5 C2 2
104 3.5 C1 3
105 3.5 C2 0
106 3.5 C3 1
107 3.5 C4 1
109 3.5 C6 4
110 3.5 C7 1

Regards,|||SELECT ssn
, SUM(case when max_credit = 0
then sem_for_max_credit
else max_credit end) as daSum
FROM (
SELECT ssn
, flag
, sem as sem_for_max_credit
, credit as max_credit
FROM daTable as T
WHERE credit =
( SELECT MAX(credit)
FROM daTable
WHERE ssn = T.ssn
AND flag = T.flag )
) AS maxes
GROUP
BY ssn|||Thanks for the help.sql