CODEWITHSUNDEEP
mysqlgroup-byanalytics
user2415706
user2415706

Reputation: 982

How to filter window function with sum in MYSQL?

How do I write this query to filter only sums that are above 50? I can't get this to work with having or sum but I'm sure there's some way.

select name, sum(score) 
    from submissions inner join hacker on submissions.hacker_id = hacker.hacker_id
    group by submissions.hacker_id
    order by sum(score) desc
    having sum(score) > 50

Fiddle with tables are here (there's nothing unusual about them, this query runs without the last line but returns everybody's scores and names): http://sqlfiddle.com/#!9/7a660d/16

Upvotes: 0

Views: 472

Answers (3)

Gerardsson
Gerardsson

Reputation: 382

You can also use a CASE in the sum-statement. This approach works in a windows function, although I have not tested it for the normal sum aggregate function Option 1 (Case in aggregate sum):

select name, sum(case when score>50 else 0 end) 
from submissions inner join hacker on submissions.hacker_id = hacker.hacker_id
group by submissions.hacker_id
order by sum(score) desc

Option 2 Windows function:

select name, 
sum(case when score>50 else 0 end) over (partition by submissions.hacker_id)
from submissions inner join hacker on submissions.hacker_id = hacker.hacker_id
group by submissions.hacker_id

The order by clause changes a bit so I left it out but you can always add it again

Upvotes: 1

Amit Verma
Amit Verma

Reputation: 2488

Order by should be in last

select name, sum(score) 
from submissions 
inner join hacker on submissions.hacker_id = hacker.hacker_id
group by submissions.hacker_id
having sum(score) > 50 
order by sum(score) desc

Upvotes: 1

ggordon
ggordon

Reputation: 10035

Your order by should be after your having. i.e.


select 
    name, 
    sum(score) 
from 
    submissions 
inner join 
    hacker on submissions.hacker_id = hacker.hacker_id
group by 
    submissions.hacker_id
having 
    sum(score) > 50 
order by 
    sum(score) desc

Upvotes: 1

Related Questions