SQL sum by multiple group

JKeags23 — Wed, 28 Jun 2017 15:35:22 GMT

I am trying to sum a metric (AllCost) by three different variables, Age, Study Type, and Year.

So the final table would look something like this.

Study	Model Year	Age	All Cost Total
Study1	2015	0	2,551
Study1	2015	30	2,659
Study1	2015	60	1,444
Study2	2015	0	2,495
Study2	2015	30	2,341
Study2	2015	60	1,535
Study3	2015	0	1,472
Study3	2015	30	2,514
Study3	2015	60	1,567
Study1	2014	0	2,756
Study1	2014	30	1,887
Study1	2014	60	2,453
Study2	2014	0	2,358
Study2	2014	30	2,255
Study2	2014	60	1,741
Study3	2014	0	1,318
Study3	2014	30	2,623
Study3	2014	60	1,300

proc sql ;
create table work.temp002 as select 
distinct
a.study,
a.model_year,
a.Claims,
sum(AllCost) as AllCostTotal					   
from work.temp001 a
where  Age in(0:10000) 
group by a.Age and a.Study and a.model_year;
quit; run;

The output I am getting: AllCostTotal is the same number for all studies and years and changes when age changes from 0 to 60, etc.

Anything you guys can do to help would be greatly appreciated.

Best,

JKeags23

EDIT: Sample Data

Study	Year	AllCost	Age
Study1	2015	4732	0
Study1	2015	3306	30
Study1	2015	2886	60
Study1	2015	3244	90
Study1	2015	2845	0
Study1	2015	2657	30
Study1	2015	2378	60
Study1	2015	1226	90
Study2	2015	2497	0
Study2	2015	2025	30
Study2	2015	4785	60
Study2	2015	1767	90
Study3	2015	1714	0
Study3	2015	1835	30
Study3	2015	1122	60
Study3	2015	2782	90
Study2	2015	3831	30
Study3	2015	1627	0
Study3	2015	2448	30
Study3	2015	4790	60
Study3	2015	2131	90
Study1	2015	4781	0
Study1	2015	1055	30
Study1	2015	3156	60
Study1	2015	3299	90
Study1	2014	4174	0
Study1	2014	3755	30
Study1	2014	1687	60
Study1	2014	2434	90
Study1	2014	2948	0
Study1	2014	1366	30
Study1	2014	3946	60
Study1	2014	4527	90
Study2	2014	2096	0
Study2	2014	1880	30
Study2	2014	3142	60
Study2	2014	3769	90
Study3	2014	1674	0
Study3	2014	1556	30
Study3	2014	3745	60
Study3	2014	4311	90
Study2	2014	4116	60
Study2	2014	3755	90
Study3	2014	1514	0
Study3	2014	4040	30
Study3	2014	1963	60
Study3	2014	3005	90
Study3	2014	1117	0
Study1	2014	3201	30
Study1	2014	2690	60
Study1	2014	2188	90

Re: SQL sum by multiple group

kiranv_ — Wed, 28 Jun 2017 15:18:37 GMT

If possible, can you add a sample data of what you have, then it would be easy for someone to help.

Re: SQL sum by multiple group

ballardw — Wed, 28 Jun 2017 16:25:26 GMT

What happens if you change

group by a.Age and a.Study and a.model_year

group by a.Age , a.Study , a.model_year

The 'And' as you are using it creates a boolean evaluation for the three variables and since they are all likely not to equal 0 (false) you only get one result: true so there is only one group.

Re: SQL sum by multiple group

JKeags23 — Wed, 28 Jun 2017 17:43:01 GMT

Such a simple, yet eloquent solution.

Thank you, Grand Advisor ballardw!

topic Re: SQL sum by multiple group in SAS Programming

SQL sum by multiple group

Re: SQL sum by multiple group

Re: SQL sum by multiple group

Re: SQL sum by multiple group