group by clause
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,
Settlem开发者_开发技巧entType varchar(5),
CreditCardNumber varchar(25),
ProductTypeCode varchar(10),
NewProductTypeCode varchar(10),
TransactionType int,
ForeignAmount money,
LocalAmount money,
ProductCode varchar(10)
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...
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
精彩评论