Solved
New Contributor
Posts: 2

SQL sum by multiple group

[ Edited ]

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

Accepted Solutions
Solution
‎06-28-2017 01:43 PM
Super User
Posts: 13,523

Re: SQL sum by multiple group

What happens if you change

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

to

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.

All Replies
PROC Star
Posts: 509

Re: SQL sum by multiple group

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

Solution
‎06-28-2017 01:43 PM
Super User
Posts: 13,523

Re: SQL sum by multiple group

What happens if you change

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

to

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.

New Contributor
Posts: 2

Re: SQL sum by multiple group

Such a simple, yet eloquent solution.