如何计算一个范围内结果数的SQL字段？ [英] How can I count an SQL field for the number of results within a range?

问题描述

我有一个表格列出发票说明，金额以及发票逾期的天数。 （一个负数表示它尚未到期，例如：-4表示在4天内到期）。

I have a table which lists invoice descriptions, the amount and how many days the invoice is overdue. (A negative number means it is not due yet, for example; -4 means that it is due in 4 days).

+------------------------------+------------+-----------+
| Description                  | Amount     | OverdueBy |
+------------------------------+------------+-----------+
|  Payment made on 17/05/13    | -149874.25 | 368       |
|  Stationary                  | 185.85     | -34       |
| .4m3 Concrete RMD            | 300.52     | -4        |
| 1 x red isolation lock       | 29.52      | -34       |
| 1.3T Concrete Lifting Clutch | 279.40     | -34       |
| 1.3T Swiftlift Clutch        | 634.63     | -34       |
| 100kg Garage Creeper         | 92.20      | 27        |
| 100x100x3.0 RHS Ptd          | 64.91      | -34       |
| 10mm Round Bar               | 502.43     | -34       |
| 10t Round Slings             | 453.20     | -34       |
| 12V 130A ALT                 | 324.50     | -4        |
| 12V 55W & 24V 70W P14 5S     | 137.28     | -4        |
| 12V Bilge Pump               | 150.95     | 26        |
| 12V Master Switch            | 286.00     | -4        |
| 14t Excavator 3103-300414    | 12540.00   | -4        |
| 150mm Blue Sunny Layflat     | 105.60     | -4        |
| 15kg Bright bullethead nails | 55.61      | -34       |
| 19 x Red isolation locks     | 542.36     | -34       |
| 2 Day Hire of HYTORC         | 330.00     | -4        |
| 2 x 1m x 1m spill trays      | 378.40     | -34       |
| 2 x Coolant 5Lt              | 45.36      | -4        |
| 2 x Printer Cartridges       | 87.90      | -34       |
| 2.1 ROADBASE                 | 6381.94    | -34       |
| 2.1 Road Base Wet            | 755.37     | -34       |
| 2.1 roadbase                 | 1311.76    | -4        |
+------------------------------+------------+-----------+

我想计算逾期的项目50天+，40-49天和30-39天，然后显示选择金额的SUM。

I want to count how many items are overdue by 50 days +, 40-49 days and 30-39 days and then display the SUM of the amount for the selection.

我的结果可能如何：

+--------------+--------------+--------------+
| OverdueRange | OverdueCount | Total Amount |
+--------------+--------------+--------------+
| 50+          | 12           | 10220        |
| 49-49        | 13           | 494          |
| 30-39        | 25           | -403         |
+--------------+--------------+--------------+

推荐答案

< >解决方案

解决方案

您可以使用CASE子句执行此操作：

You can do this using a CASE clause:

SELECT CASE WHEN OverdueBy >= 50 THEN '50+' 
WHEN OverdueBy >= 40 and OverdueBy < 50 THEN '40-49'
WHEN OverdueBy >= 30 and OverdueBy < 40 THEN '30-39'
.....
ELSE 'Less than that' END AS OverdueRange,
Count(*) as OverdueCount,
Sum(Amount) as [Total Amount]
From yourtable
group by CASE WHEN OverdueBy >= 50 THEN '50+' 
WHEN OverdueBy >= 40 and OverdueBy < 50 THEN '40-49'
WHEN OverdueBy >= 30 and OverdueBy < 40 THEN '30-39'
.....
ELSE 'Less than that' END 

这篇关于如何计算一个范围内结果数的SQL字段？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！