Home » SQL & PL/SQL » SQL & PL/SQL » Regarding Aggregate conditions ..
Regarding Aggregate conditions .. [message #284422] Thu, 29 November 2007 11:02 Go to next message
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

Re: Regarding Aggregate conditions .. [message #284427 is a reply to message #284422] Thu, 29 November 2007 11:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64269
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

Post a test case: create table and insert statements.
I don't understand why 4 is not in the sum of 101.

Re: Regarding Aggregate conditions .. [message #284448 is a reply to message #284422] Thu, 29 November 2007 13:06 Go to previous messageGo to next message
Messages: 4669
Registered: February 2005
Location: East Coast USA
Senior Member
Have you EVER posted anything here with correct formatting?
Re: Regarding Aggregate conditions .. [message #284486 is a reply to message #284422] Thu, 29 November 2007 21:05 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
while you are figuring out how to format your posts, do a google on Oracle Analytics.

Good luck, Kevin
Previous Topic: Column Addition and Update on Big table
Next Topic: creating a view at run-time by using pl/sql procedure
Goto Forum:

Current Time: Mon Jan 16 05:16:40 CST 2017

Total time taken to generate the page: 0.14784 seconds