Skip to content
Advertisement

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

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.

Advertisement

Answer

You can do:

select *
from (
  select *, sum(amount) over(partition by week) as sa
  from t
) x
where sa >= 40
10 People found this is helpful
Advertisement