ORACLE SQL: Multiple SUMS dependent on CODE in One Statement

Question

I believe there is a way to do this, but I'm not familiar with ORACLE 10g as many other people are. Here's the scenario:

I'm currently converting Classic ASP pages to ASP.net 2.0. I have a query that is creating a report. It's reporting Sales vs. Previous Sales. What is happening currently is one query is going out to the database and grabbing a complete list of locations that sell our products. Then, it loops through every single row of the locations, and runs some summing operations in SQL.

It goes out to a few other tables, sums up sales quantities, then adds the sum to a table row, etc. Since the locations query returns a lot of results, the query takes a good 2-3 minutes.

My question is how can I consolidate these all into one query.

LOCATIONS QUERY:

SELECT DISTINCT t.location, 
  l.city, 
  f.year, 
  f.customer FROM loc t, 
location l, father_table f 
WHERE f.number = t.number(+) 
AND f.code = '0001' 
AND f.c_code = '01' 
AND t.location= l.code(+) 
AND t.code IN ('C', 'S') 
AND t.co_code IN ('G', 'V', 'A', 'D') 
AND t.year = '2008' 
ORDER BY l.city, f.year

The sum query for each of the rows in the above query is this:

SELECT SUM(nvl(t.sale_quantity,0)) sale_quantity 
FROM loc t, father_table f  
WHERE f.number = t.number(+) 
AND f.code = '0001' 
AND f.c_code = '01'
AND f.year = '2008' 
AND t.code = 'C' 
AND t.location = '1566' <----- EACH ROW'S t.location VALUE
AND t.co_code IN ('G', 'V', 'A', 'D') 
GROUP BY t.location, t.code, f.year

Instead of looping through each record of the original query, is there a way I can combine the queries and have the SUM in the Location's query. The key here is that the second query only gets the sales SUM when the t.code = 'C' not 'C' & 'S'

Answer 1

Nevermind,

I ask the question, then find the answer myself. I suppose that's a good thing. I haven't actually used the SUM( CASE ) keywords before. It worked like a charm though.

Answer 2

I think this does what you want. If it's not exactly right, I think the key thing you need to know about is the CASE expression; this is a way to filter within the SUM function.

SELECT t.location, 
  l.city, 
  f.year, 
  f.customer,
  SUM( NVL( CASE WHEN t.code ='C' THEN t.sale_quantity ELSE 0 END, 0)) sale_quantity
FROM loc t, location l, father_table f 
WHERE f.number = t.number(+) 
AND f.code = '0001' 
AND f.c_code = '01' 
AND t.location= l.code(+) 
AND t.code IN ('C', 'S') 
AND t.co_code IN ('G', 'V', 'A', 'D') 
AND t.year = '2008' 
GROUP BY t.location, l.city, f.year
ORDER BY l.city, f.year