Find a statistical statement with a single record with the highest limit

落日圣骑

The structure of the table is simple
C_name, C_no, C_amnt, which are name, serial number, and amount
Content example:
A, 1,120.00
A, 2,150.00
A, 3,400,00
A, 4,240.00
B, 5,730.00
B.6.280.00
C, 7,30.00

The requirements are as follows. For each record with C_amnt> = 300, it is counted as 300, and then group by C_name to get its sum (C_amnt) and resumed sum (C_amnt).

This is mainly used in the lottery. There is a chance for a lottery every 100 yuan. There is a maximum of three opportunities for each product. The balance can be accumulated as one opportunity. As the above data: It should be output as:
A, 910.00,8 --- 8 = int ((120 + 150 + 300 + 240) / 100)
B, 1010.00,5 --- 5 = int ((300 + 280) / 100)
C, 30.00,0 --- 0 = int (30/100)

After thinking about it for a long time, I can't figure out how to write the condition, because the original table is data on the official server. I can't add a field to it and then update the highest value of each. Is it feasible to build a temporary table? Because that table has nearly 3 million data (all the flow since the opening). It's best if you can use a sentence, huh, huh.

Thank you in advance.

fanfan1980

select C_name, C_amnt = sum (C_amnt), times = cast (sum (case when C_amnt> = 300 then 300.00 else C_amnt end / 100) as int) from T group by C_name

fanfan1980

create table T (C_name char (1), C_no int, C_amnt decimal (10, 2))

insert T select 'A', 1, 120.00
union all select 'A', 2, 150.00
union all select 'A', 3, 400.00
union all select 'A', 4, 240.00
union all select 'B', 5, 730.00
union all select 'B', 6, 280.00
union all select 'C', 7, 30.00

select C_name, C_amnt = sum (C_amnt),
Times = cast (sum (case when C_amnt> = 300 then 300.00 else C_amnt end / 100) as int)
from T group by C_name

--result
C_name C_amnt times
------ ---------------------------------------- ---- -------
A 910.00 8
B 1010.00 5
C 30.00 0

(3 row (s) affected)

落日圣骑

Thank you very much for your help upstairs. It seems that I have a lot to learn about SQL ...