如何计算特定范围内的交易数量(How to calculate number of transactions within a specific range)

如何计算特定范围内的交易数量(How to calculate number of transactions within a specific range)

任何人都可以帮助解释如何使用TSQL计算去年销售量在此范围内的交易次数。 我不知道如何填充该范围，并将其与我的销售总额链接到数据库中

0.01- 5.00, 5.001 -10.00, 10.01 - 15.00

我曾尝试使用下面的代码，

SELECT COUNT (*) as Nooftransactions, Floor (Amount)/5 As Range, Convert(Varchar(max), (5*Floor(Linetotal/5))) +'0.01' + '-' + Convert(Varchar(max), (5*floor(Linetotal/5) +5))) + '0.00' as Rangesplit Group by Amount,Floor (Amount)/5 , Convert(Varchar(max), (5*Floor(Linetotal/5))) +'0.01' + '-' + Convert(Varchar(max), (5*floor(Linetotal/5) +5))) + '0.00' Order Amount

Can anybody help on how to calculate number of transactions within this range for last year sales using TSQL. I don't have a clue on how to populate that range and link it with my sales total in the database

0.01- 5.00, 5.001 -10.00, 10.01 - 15.00

I have tried to use this code below,

SELECT COUNT (*) as Nooftransactions, Floor (Amount)/5 As Range, Convert(Varchar(max), (5*Floor(Linetotal/5))) +'0.01' + '-' + Convert(Varchar(max), (5*floor(Linetotal/5) +5))) + '0.00' as Rangesplit Group by Amount,Floor (Amount)/5 , Convert(Varchar(max), (5*Floor(Linetotal/5))) +'0.01' + '-' + Convert(Varchar(max), (5*floor(Linetotal/5) +5))) + '0.00' Order Amount

最满意答案

有点

select ranges.lo, ranges.hi, count(*) as [number of transactions] from transactions t join ( values (0.01, 5.00), (5.001, 10.00), (10.01, 15.00) ) ranges(lo, hi) on t.value between ranges.lo and ranges.hi group by ranges.lo, ranges.hi order by ranges.lo;

根据真实的表格结构进行调整。

Kind of

select ranges.lo, ranges.hi, count(*) as [number of transactions] from transactions t join ( values (0.01, 5.00), (5.001, 10.00), (10.01, 15.00) ) ranges(lo, hi) on t.value between ranges.lo and ranges.hi group by ranges.lo, ranges.hi order by ranges.lo;

Adjust according to the real table structure.