November 29, 2007 at 10:04 am
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
idssncreditflagsem
11010C90
21014C93
31014.5C92
41013.5C11
51024.2C33
61030C12
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
101C13.5
103C12.0
102C34.2
101C98.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,
November 29, 2007 at 1:12 pm
I could not follow what you are wanting if the credit value is 0 but other than that I played with it for a while and came up with this:
Create Table #value (
ssn int,
flag varchar(5),
credit decimal(3,1))
insert into #value select distinct ssn, max(flag), max(credit)
from sample
group by ssn, flag
order by SSN
select ssn, sum(credit)
from #value
group by ssn
drop table #value
It produces an output of:
ssn(No column name)
1018.0
1024.2
1030.0
Hopefully someone else can help with correcting the 103 value.
Dep
November 29, 2007 at 1:48 pm
Note: this is NOT going to scale well. If you need to do this against something big, you should be able to go get lunch, it is going to take a while (or - tell us that's true and we'll design something efficient).
select
ssn,
flag,
sum(maxcred) total
from
(select ssn, flag, max(credit) maxcred from tmpltable group by ssn,flag) T
group by ssn, flag
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 29, 2007 at 2:22 pm
oops - let's revise that one little bit:
select ssn, sum(maxcred) total from
(select ssn, flag, max(credit) maxcred from tmpltable group by ssn,flag) T
group by ssn
You only need to group by flag on the INNER query.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 30, 2007 at 11:57 pm
Thanks Guys.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply