## Sum and Group by

Solved
Regular Contributor
Posts: 168

# Sum and Group by

I've a data as follows.

 ticket_count resolved_by percentage_of_tickets_resolved 4 L2 19.05% 7 L2 33.33% 5 L2 23.81% 4 L3 19.05% 1 L3 4.76%

I just want to sum the variables ticket_count and percentage_of_tickets_resolved and group by the varaible resolved_by to display the  the data as follows.

 ticket_count resolved_by percentage_of_tickets_resolved 16 L2 76.19% 5 L3 23.81%

When I tried the below code, I received the error as

``````26         proc sql;
27         select *,sum(ticket_count) as ticket_counts,sum(percentage_of_tickets_resolved) as t_percentage_of_tickets_resolved
28         from summary1
29         group by  ticket_counts
30         having (calculated ticket_counts)>1;
ERROR: Summary functions are restricted to the SELECT and HAVING clauses only.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
31         quit;``````

I think there is an issue with having clause in my code. Appreciate if someone help me to overcome this issue.

Accepted Solutions
Solution
‎08-07-2017 09:49 AM
PROC Star
Posts: 1,283

## Re: Sum and Group by

``````data have;
input ticket_count resolved_by\$ percentage_of_tickets_resolved;
datalines;
4 L2 19.05
7 L2 33.33
5 L2 23.81
4 L3 19.05
1 L3 4.76
;

proc sql;
create table want as
select sum(ticket_count) as ticket_count_sum
,resolved_by
,sum(percentage_of_tickets_resolved) as percentage_of_tickets_resolved
from have
group by resolved_by
having ticket_count_sum > 1;
quit;
``````

All Replies
Solution
‎08-07-2017 09:49 AM
PROC Star
Posts: 1,283

## Re: Sum and Group by

``````data have;
input ticket_count resolved_by\$ percentage_of_tickets_resolved;
datalines;
4 L2 19.05
7 L2 33.33
5 L2 23.81
4 L3 19.05
1 L3 4.76
;

proc sql;
create table want as
select sum(ticket_count) as ticket_count_sum
,resolved_by
,sum(percentage_of_tickets_resolved) as percentage_of_tickets_resolved
from have
group by resolved_by
having ticket_count_sum > 1;
quit;
``````
Super User
Posts: 9,599

## Re: Sum and Group by

Have you tried replacing:

`having (calculated ticket_counts)>1;`

With:

`having sum(ticket_count > 1;`

Again, its highly advisable to add Test data in the form of a datastep so that we can check.

☑ This topic is solved.