SQL GROUP BY and HAVING but have results include column not included in GROUP BY to provide more detail?

user_826737 0
Viewed 31

Sample data:

Week Client Amount
1 A 20
1 B 20
2 A 20
2 B 10
3 C 40

I want a query where if the total for the week is 40 or more, include those rows in the result set, but ALSO include the Client.

So the results would look something like this:

Week Client Amount
1 A 20
1 B 20
3 C 40

I am prepared to do this via two queries and EXISTS, but I was checking to see if there was something more elegant. I've tried using a GROUP BY and HAVING but no such luck as I don't think I can use having on the aggregate but also show the client detail.

sql
1 Answers

You can do:

select *
from (
  select *, sum(amount) over(partition by week) as sa
  from t
) x
where sa >= 40
user_64361910
Related