CODEWITHSUNDEEP
sql-serversql-server-2005
Saravanan Ramalingam
Saravanan Ramalingam

Reputation: 13

group by clause in sql server 2005

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.

Upvotes: 1

Views: 1190

Answers (3)

Martin Smith
Martin Smith

Reputation: 452998

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

Upvotes: 1

Michael Pakhantsov
Michael Pakhantsov

Reputation: 25370

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

Upvotes: 0

Denis Valeev
Denis Valeev

Reputation: 6015

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

Upvotes: 0

Related Questions