PJC83 PJC83 - 1 month ago 5
SQL Question

SQL - Grouping COUNT Results

The query I'm trying to answer is 'How many sales above or equal to 60 has each person made?'

My table (sales$):

SaleID name salevalue
1 Steve 100
2 John 50
3 Ellen 25
4 Steve 100
5 Mary 60
6 Mary 80
7 John 70
8 Mary 55
9 Steve 65
10 Ellen 120
11 Ellen 30
12 Ellen 40
13 John 40
14 Mary 60
15 Steve 50


My code is:

select name,
COUNT(*) as 'sales above 60'
from Sales$
group by salevalue, name
having salevalue >= 60;


Which gives:

Ellen 1
John 1
Mary 2
Mary 1
Steve 1
Steve 2


The information is correct in that Mary & Steve both have 3 sales, however I'm forced by the HAVING command to group them out.

Any ideas? I'm sure I've just taken a wrong turning.

Answer

You can use conditional aggregation for this:

select name,
       COUNT(case when salevalue >= 60 then 1 end) as 'sales above 60'
from Sales$
group by name

This way COUNT will take into consideration only records having salevalue >= 60.