如何计算特定范围内的交易数量(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 AmountCan 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.00I 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.
更多推荐
发布评论