group by clause in sql server 2005

Question

i have table called table1 and it looks like the below

    record type tran_ref_number  amount              customer_name  

    TRN                 123     15000                    sara
    TRN                 234     25000                    inba
    TRN                 345     20000                    rajiv
    TRN                 456     16000                    rahul
    TRN                 567     34556                    sathish
    TRN                 678     15000                    ilango
    TRN                 123     15000                    sara
    TRN                 234     25000                    inba
    TRN                 345     20000                    rajiv
    TRN                 456     16000                    rahul
    TRN                 567     34556                    sathish
    TRN                 678     15000                    ilango

i want to delete records from this table on the basis of "if the sum of the amount is exceeding 70000/per customer". the result should be grouped on the basis of customer name.

Any one who has any idea about this can help me out on this issue.

thanks in advance.

Answer 1

select customer_name
from table1 t
group by customer_name
having sum(amount) > 70000

Answer 2

  DELETE FROM Table1
  WHERE CUSTOMER_NAME IN (SELECT customer_name
     from table1 t
     group by customer_name
     having sum(amount) > 70000
  )

Answer 3

;with cte as
(
SELECT SUM(amount) OVER (PARTITION BY customer_name) a
FROM table1
)
DELETE FROM cte WHERE a > 70000