how to pull records that have a total value greater than something?

[oh704]

Hi All,

I'm sorta newbie on SQL, and i'm trying to write a sql that will pull all records where it's total amount if greater than an amount.

ie.

Name $
John 2
John 3
John 4
John 5
Mike 1
Mike 2
Mike 3
Kate 9

i want to pull all the records for each person with an aggregate amount of greater than $10, so in this sample, the 4 records for John.

i can achieve this effect by joining two queries together in Access by name. But I need this to be in a single sql format because it gets built dynamically through some vb codes.

any advise is much appreciated.
Thanks

 

[markros]

If you're using latest version of SQL Server, then

;with cte as (select Name, Amount, sum(Amount) over (partition by [Name]) as Total form MyTable)

select * from cte where Total > @PassedAmount

If you only want to select Name and Total Amount, then

select [Name], sum(Amount) as TotalAmount
from MyTable group by [Name] having sum(Amount) > @PassedAmount

Take a look at HAVING clause for the GROUP BY in BOL

PluralSight Learning Library