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.

Answer

You can do:

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

Advertisement

Answer