group by clause

Question

i have a table called table1 and it has following columns.

suppose there are records like localamount is 20,000, 30000,50000, 100000 then as per my condition i have to delete records from this table according to the group by set of site id, till id, transid,shift id where localamount exceeds 10,000... the rest of the records can be available?

my aim is to delete rows from this table where local amount is exceeds 10,0000 according to site id, till id, transid,shift id

    SiteId          varchar(10), 
    TillId          tinyint,
    ShiftId         int,
    TransId         int,
    TranDate        datetime,
    SettlementType  varchar(5),
    CreditCardNumber varchar(25),
    ProductTypeCode varchar(10),
    NewProductTypeCode varchar(10),
    TransactionType int,
    ForeignAmount   money,
    LocalAmount     money,
    ProductCode     varchar(10)

Answer 1

Im not sure I understand what you are saying, but couldn't you do this without a group by?

delete from table1 where LocalAmount > 10,0000[sic] and SiteId = whatever and TillId = whatever...

obviously take the [sic] out...

Answer 2

Assuming you want to delete the whole group where the sum is > 10000

;with cte as
(
select sum(localamount) over
            (partition by siteid, tillid, transid,shiftid) as l,
* from table1 
)
delete from cte where l>10000