ORACLE SQL: multiple SUMS dependent on CODE in one statement
I believe there is a way to do this, but I am not familiar with ORACLE 10g like many others. Here's the script:
I am currently converting classic ASP pages to ASP.net 2.0. I have a query that generates a report. He reports sales versus previous sales. What is currently happening is one query that goes into the database and grabs the full list of places where our products are sold. Then it goes through each line of locations and runs some summing operations in SQL.
It goes out to several other tables, sums up the sales volumes, then adds the amount to the table row, etc. Since the location query returns many results, the query takes a good 2-3 minutes.
My question is how can I combine it all into one query.
LOCATION:
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 request for each of the rows in the above query is as follows:
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 t.location VALUE
AND t.co_code IN ('G', 'V', 'A', 'D')
GROUP BY t.location, t.code, f.year
Rather than iterating over each record of the original request, is there a way I can combine requests and have a SUM in the Location request. The key point here is that the second query only gets the SUM of sales when t.code = 'C' not 'C' and 'S'
I think this does what you want. If this is not entirely correct, I think the key point you need to be aware of is the CASE expression; it is a way to filter inside 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
source to share