|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| ||||||||||||||||||||||||||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
June 9th, 2009, 11:25 AM
|
|||
|
|||
|
Complex query
Hi guys,
Sorry about the title, that's as much as I can describe it right now. I need to run a bunch of counts and sum on one table and return different fields for each aggregate. It is giving me a headache. I'm sure there is a cleaner way of doing this than what I am doing now. This is a sample data: QUOTE_NAME CREATE_DATE PRICE SUPPLIER_NAME abc 29-APR-09 347 SUP1 abc 29-APR-09 155 SUP1 abc 29-APR-09 0 SUP1 abc 29-APR-09 0 SUP1 abc 29-APR-09 0 SUP1 abc 29-APR-09 0 SUP1 abc 29-APR-09 0 SUP1 abc 29-APR-09 0 SUP1 def 24-APR-09 1004 SUP2 def 24-APR-09 680 SUP2 def 24-APR-09 170 SUP2 def 24-APR-09 200 SUP2 def 24-APR-09 200 SUP1 def 24-APR-09 692 SUP1 def 24-APR-09 692 SUP1 def 24-APR-09 3041.99 SUP1 def 24-APR-09 857 SUP1 ghi 22-SEP-08 155 SUP1 ghi 22-SEP-08 155 SUP2 ghi 22-SEP-08 155 SUP2 ghi 22-SEP-08 155 SUP2 ghi 22-SEP-08 909.99 SUP3 ghi 22-SEP-08 297 SUP3 ghi 22-SEP-08 649 SUP3 ghi 22-SEP-08 240 SUP3 ghi 22-SEP-08 122.99 SUP3 I need to get this QUOTE_NAME SUP1 SUP2 SUP3 Sum def 5 4 0 7536.99 abc 8 0 0 502 ghi 1 3 5 2838.98 And so far, the SQL: Code:
select distinct q1.quote_name as quote_name, (select count(*) from quote_table bc_i1 where bc_i1.quote_id= q1.quote_id and bc_i1.supplier_name='SUP1') as SUP1, (select count(*) from quote_table bc_i2 where bc_i2.quote_id= q1.quote_id and bc_i2.supplier_name='SUP2') as SUP2, (select count(*) from quote_table bc_i2 where bc_i2.quote_id= q1.quote_id and bc_i2.supplier_name='SUP3') as SUP3 FROM quote_table q1 This is a horrible query. It is not flexible. If I have a SUP4 tomorrow, I'll have to edit it. Second, it is slow Third, I am not including the sum of price I might have to break that down. Maybe even incorporate PL/SQL? Any help would be appreciated. PS: I'd like to format the tables for better readability... Can I do that?
|
|
#2
June 9th, 2009, 12:25 PM
|
|||
|
|||
|
Try this:
Code:
select quote_name,
sum(case when supplier_name = 'SUP1' then 1 else 0 end) as SUP1,
sum(case when supplier_name = 'SUP2' then 1 else 0 end) as SUP2,
sum(case when supplier_name = 'SUP3' then 1 else 0 end) as SUP3
FROM quote_table
GROUP BY quote_name;
Quote:
|
|
#3
June 9th, 2009, 12:35 PM
|
|||
|
|||
|
Quote:
This may be your most effective solution. You can probably limit the number of times you retrieve the sorted result set to one and use procedural code (PL/SQL) to accumulate your values as you walk the result set. I think there are also some newer cross-tab SQL extensions in Oracle that might be helpful; but I have not personally been using them. Clive
|
|
#4
June 9th, 2009, 01:29 PM
|
|||
|
|||
|
Thanks clivew and shammat! That answers a lot already
 Quote:
Yes, shammat, I need both the count of items and the sum of price in this case. The reason I didn't include it was that the query was already taking ages to run. Adding another sum() would have made it worse. I understand the CASE lines, but I don't know how to make a sum(price) fit. clive, I'm goggling "cross-tab SQL extensions" big time  EDIT  Silly me Code:
sum(CASE WHEN supplier_name='SUP1' then ext_price end) as SumPriceSUP1
|
|
#5
June 9th, 2009, 01:42 PM
|
||||
|
||||
Rollup
If you have 10g+ you can use the GROUP BY ROLLUP option to get the totals.
If your version is 11g, then check out the PIVOT function. 
__________________
|
|
| Viewing: Dev Shed Forums > Databases > Oracle Development > Complex query |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
