code_junkie

ORACLE SQL: Multiple SUMS dependent on CODE in One Statement

Nov 12th, 2011
57
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.05 KB | None | 0 0
  1. SELECT DISTINCT t.location,
  2. l.city,
  3. f.year,
  4. f.customer FROM loc t,
  5. location l, father_table f
  6. WHERE f.number = t.number(+)
  7. AND f.code = '0001'
  8. AND f.c_code = '01'
  9. AND t.location= l.code(+)
  10. AND t.code IN ('C', 'S')
  11. AND t.co_code IN ('G', 'V', 'A', 'D')
  12. AND t.year = '2008'
  13. ORDER BY l.city, f.year
  14.  
  15. SELECT SUM(nvl(t.sale_quantity,0)) sale_quantity
  16. FROM loc t, father_table f
  17. WHERE f.number = t.number(+)
  18. AND f.code = '0001'
  19. AND f.c_code = '01'
  20. AND f.year = '2008'
  21. AND t.code = 'C'
  22. AND t.location = '1566' <----- EACH ROW'S t.location VALUE
  23. AND t.co_code IN ('G', 'V', 'A', 'D')
  24. GROUP BY t.location, t.code, f.year
  25.  
  26. SELECT t.location,
  27. l.city,
  28. f.year,
  29. f.customer,
  30. SUM( NVL( CASE WHEN t.code ='C' THEN t.sale_quantity ELSE 0 END, 0)) sale_quantity
  31. FROM loc t, location l, father_table f
  32. WHERE f.number = t.number(+)
  33. AND f.code = '0001'
  34. AND f.c_code = '01'
  35. AND t.location= l.code(+)
  36. AND t.code IN ('C', 'S')
  37. AND t.co_code IN ('G', 'V', 'A', 'D')
  38. AND t.year = '2008'
  39. GROUP BY t.location, l.city, f.year
  40. ORDER BY l.city, f.year
Add Comment
Please, Sign In to add comment