group by clause query

Question

i have a table with values like this,

i want to group by cusotmer name where as sum of the local amount should exceed 50000 else i need to delete those records which do not satisfy?

how to achieve it in sql server 2005?

TRN 259 3   9/9/2010    6622    68667(Rs) ABHIJIT KATARE  
TRN 260 3   9/9/2010    6622    14635(Rs)   ABHIJIT KATARE  
TRN 235 3   9/9/2010    6586    68128   AJAY PARASRAMPURIA  
TRN 236 3   9/9/2010    6586    14490   AJAY PARASRAMPURIA  
TRN 257 3   9/9/2010    6621    68667   ANAND DESAI  
TRN 258 3   9/9/2010    6621    14635   ANAND DESAI  
TRN 287 3   9/9/2010    6817    119095  ANAND KATAKAM  
TRN 242 3   9/9/2010    6594    95689   ANILKUMAR MUTHUNPARA  
TRN 211 3   9/9/2010    6507    52239   ARBIND KUMAR GUPTA  
TRN 212 3   9/9/2010    6538    63183   ASHOK KELKAR  
TRN 185 3   9/9/2010    6431    140610  BANSAL Y.K  
TRN 186 3   9/9/2010    6431    46845   BANSAL Y.K  
TRN 248 3   9/9/2010    6600    72565   BENNO HANS LUKE  

Answer 1

This will show you the customers that have less than 50000:

select CustomerName, sum(LocalAmount)
from MyTable
group by CustomerName
having sum(LocalAmount) <= 50000

Note that you are better off grouping by CustomerID if there is one, as there could be duplicate names. Then, you can delete by doing this:

delete from MyTable 
where CustomerID in (
    select CustomerID 
    from MyTable
    group by CustomerID 
    having sum(LocalAmount) <= 50000
)

Answer 2

Try this for the select:

SELECT * FROM Table
GROUP BY CustomerName
HAVING SUM(LocalAmount) > 50000

Try this for delete (modified version of @RedFilter's):

delete from MyTable 
where CustomerName in (
    select CustomerName 
    from Table
    group by CustomerName 
    having sum(LocalAmount) <= 50000
)

Answer 3

select * from MyTable group by CustomerName having sum(LocalAmount) > 50000

Answer 4

to see the good rows use:

SELECT
    cusotmerName ,SUM(localAmount)
    FROM YourTable
    GROUP BY cusotmerName
    HAVING SUM(localAmount)>50000 

to delete the bad rows use:

DELETE YourTable
WHERE
    CustomerName IN (SELECT
                             cusotmerName
                             FROM YourTable
                             GROUP BY cusotmerName
                             HAVING SUM(localAmount)<50000 
                        )

Answer 5

this syntax looks a bit weird but it works!

;with cte as
(
select sum(LocalAmount) over (partition by cname) s 
from yourtable
)
delete from cte where s <= 50000