How to sum up only specific values in group by

Question

I have the following data

Name Place Stores Sales
A X1 10 $100
A X2 20 $150
B X2 25 $200
B X3 30 $300
B X4 40 $400
C X1 30 $300
D X2 40 $400
D X3 50 $500

I want to get the Group By Statement to give Name wise Sum of Stores, Sales and Count of Observations (for that Name) only if Stores in that observation are > 20.

So in this example A will not be there in the result at all. Sum of B will be there only with stores 25 and 30 and so on.

How to write SQL query for this.

so is name or place observation? or does it have it's own rules? perhaps Select name, Sum(Stores), sum(Sales), count(Place) as Observations From data Where stores > 20 group by name — xQbert

BlueCucumber BlueCucumber · Accepted Answer · 2016-08-25T19:06:21

Use a WHERE statement with the clause defining your filter criteria. WHERE occurs before GROUP BY.

proc sql;
title 'My Query';
select Name, sum(Stores) as TotalStores
   from sql.mydata
   where Stores > 20
   group by Name, TotalStores;

How to sum up only specific values in group by

1 Answers