sql - How to calculate the individual sums of multiple columns in a single query -
using oracle 11gr2 on windows 7 client. have following sample table:
yr mnth region city handled_package expected_missing_package actual_missing_package 2012 november western san fransisco 200 10 5 2012 november western los angeles 400 20 15 2012 november eastern new york 300 15 20 2012 november western seattle 100 5 7 2012 november eastern philadelphia 200 10 12 2012 november midwest chicago 300 15 15 2012 december midwest detroit 50 3 2 2012 december western los angeles 300 15 19 2012 december eastern new york 400 20 16 2012 december eastern philadelphia 150 8 6 2012 december midwest chicago 200 10 10 2013 january eastern new york 200 10 9 2013 january midwest chicago 100 5 7 2013 january western san fransisco 500 25 22 2013 january western los angeles 200 10 8 2013 january europe london 500 25 27 2013 january europe paris 400 20 23 2013 februray europe london 200 10 9 2013 februray eastern philadelphia 300 15 13 2013 februray eastern ny 400 20 18 2013 february western san fransisco 400 20 12 2013 februray western la 200 10 8 2013 februray midwest detroit 300 15 13 i want make view out of store sums of number columns (i.e. handled_package, expected_missing_package, actual_missing_package) each month of year each location. i.e. output should following:
yr mnth region handled_package expected_missing_package actual_missing_package 2012 november eastern 500 25 32 2012 november europe 0 0 0 2012 november midwest 300 15 15 2012 november western 700 35 27 2012 december eastern 250 13 12 2012 november europe 0 0 0 2012 december midwest 550 28 22 2012 december western 300 15 19 2013 january eastern 200 10 9 2013 january europe 900 45 50 2013 january midwest 100 5 7 2013 january western 700 35 30 2013 february eastern 700 35 31 2013 february europe 200 10 9 2013 february midwest 300 15 13 2013 february western 600 30 20 how that? convenience here create , insert scripts:
create table test ( yr varchar2(5), mnth varchar2(50), region varchar2(50), city varchar2(50), handled_package number, expected_missing_package number, actual_missing_package number ); insert test (yr, mnth, region, city, handled_package, expected_missing_package, actual_missing_package) values ('2012', 'november', 'western', 'san fransisco', 200, 10, 5); insert test (yr, mnth, region, city, handled_package, expected_missing_package, actual_missing_package) values ('2012', 'november', 'western', 'los angeles', 400, 20, 15); insert test (yr, mnth, region, city, handled_package, expected_missing_package, actual_missing_package) values ('2012', 'november', 'eastern', 'new york', 300, 15, 20); insert test (yr, mnth, region, city, handled_package, expected_missing_package, actual_missing_package) values ('2012', 'november', 'western', 'seattle', 100, 5, 7); insert test (yr, mnth, region, city, handled_package, expected_missing_package, actual_missing_package) values ('2012', 'november', 'eastern', 'philadelphia', 200, 10, 12); insert test (yr, mnth, region, city, handled_package, expected_missing_package, actual_missing_package) values ('2012', 'november', 'midwest', 'chicago', 300, 15, 15); insert test (yr, mnth, region, city, handled_package, expected_missing_package, actual_missing_package) values ('2012', 'december', 'midwest', 'detroit', 50, 3, 2); insert test (yr, mnth, region, city, handled_package, expected_missing_package, actual_missing_package) values ('2012', 'december', 'western', 'los angeles', 300, 15, 19); insert test (yr, mnth, region, city, handled_package, expected_missing_package, actual_missing_package) values ('2012', 'december', 'eastern', 'new york', 400, 20, 16); insert test (yr, mnth, region, city, handled_package, expected_missing_package, actual_missing_package) values ('2012', 'december', 'eastern', 'philadelphia', 150, 8, 6); insert test (yr, mnth, region, city, handled_package, expected_missing_package, actual_missing_package) values ('2012', 'december', 'midwest', 'chicago', 200, 10, 10); insert test (yr, mnth, region, city, handled_package, expected_missing_package, actual_missing_package) values ('2013', 'january', 'eastern', 'new york', 200, 10, 9); insert test (yr, mnth, region, city, handled_package, expected_missing_package, actual_missing_package) values ('2013', 'january', 'midwest', 'chicagio', 100, 5, 7); insert test (yr, mnth, region, city, handled_package, expected_missing_package, actual_missing_package) values ('2013', 'january', 'western', 'san fransisco', 500, 25, 22); insert test (yr, mnth, region, city, handled_package, expected_missing_package, actual_missing_package) values ('2013', 'january', 'western', 'los angeles', 200, 10, 8); insert test (yr, mnth, region, city, handled_package, expected_missing_package, actual_missing_package) values ('2013', 'january', 'europe', 'london', 500, 25, 27); insert test (yr, mnth, region, city, handled_package, expected_missing_package, actual_missing_package) values ('2013', 'january', 'europe', 'paris', 400, 20, 23); insert test (yr, mnth, region, city, handled_package, expected_missing_package, actual_missing_package) values ('2013', 'februray', 'europe', 'london', 200, 10, 9); insert test (yr, mnth, region, city, handled_package, expected_missing_package, actual_missing_package) values ('2013', 'februray', 'eastern', 'philadelphia', 300, 15, 13); insert test (yr, mnth, region, city, handled_package, expected_missing_package, actual_missing_package) values ('2013', 'februray', 'eastern', 'ny', 400, 20, 18); insert test (yr, mnth, region, city, handled_package, expected_missing_package, actual_missing_package) values ('2013', 'februray', 'western', 'san fransisco', 400, 20, 12); insert test (yr, mnth, region, city, handled_package, expected_missing_package, actual_missing_package) values ('2013', 'februray', 'western', 'la', 200, 10, 8); insert test (yr, mnth, region, city, handled_package, expected_missing_package, actual_missing_package) values ('2013', 'februray', 'midwest', 'detroit', 300, 15, 13); commit; will appreciate or suggestion.
thanks
seems simple group by;
select yr,mnth,region, sum(handled_package) handled_package, sum(expected_missing_package) expected_missing_package, sum(actual_missing_package) actual_missing_package test group yr,mnth,region order yr,mnth,region your update complicates things somewhat, joins in non existing regions 0 rows , uses over (partition by) instead of group by;
select distinct r.yr,r.mnth,r.region, coalesce(sum(t.handled_package) on (partition r.yr,r.mnth,r.region), 0) handled_package, coalesce(sum(t.expected_missing_package) on (partition r.yr,r.mnth,r.region), 0) expected_missing_package, coalesce(sum(t.actual_missing_package) on (partition r.yr,r.mnth,r.region), 0) actual_missing_package test t right join ( select distinct t1.region,t2.yr,t2.mnth test t1 cross join test t2 ) r on t.yr = r.yr , t.mnth=r.mnth , t.region=r.region order r.yr,r.mnth,r.region the latter can simplified, must admit oracle's analytic functions aren't specialty :)
Comments
Post a Comment