按参数将数据放入范围

ivias 发表于 Dev

伊维亚斯

我有这样的数据：

FIELD_ID | Acreage | Association
017-8596 | 1.2589  | ABC
017-8521 | 25.89   | CDA

我想首先得到Acreages范围内的字段数：

然后得到相同的范围，但乘以Association。

结果应该是这样的：

Acreage Range | Number of Fields
1-10          | 200
11-25         | 670
25-50         | 12

等等

第一个结果应该带有对所有Associations的查询，然后另一个结果应该是个人的结果Association。

伊维亚斯

我尝试了这个并获得了结果，但是想知道为什么如果我的所有行都无效并且都具有数据，我却获得无效的面积。知道会触发什么吗？

     WITH AcreageData as
(
  SELECT [FIELD_ID], 
     [ASSN],
     [ACREAGE] AS ACREAGE
  FROM dbo.CaneParcel    


),
GroupAcreage AS
(
  SELECT [FIELD_ID],      
     CASE                      
         WHEN ACREAGE <= 0.50 THEN '< 0.5 Acre'
         WHEN ACREAGE >= 0.50 AND ACREAGE <= 1.01 THEN '0.5 - 1 Acre'
         WHEN ACREAGE >= 1.01 AND ACREAGE <= 10.01 THEN '1 - 10 Acres'
         WHEN ACREAGE >= 10.01 AND ACREAGE <= 25.01 THEN '10 - 25 Acres'         
         WHEN ACREAGE >= 25.01 AND ACREAGE <= 50.01 THEN '25 - 50 Acres'
         WHEN ACREAGE BETWEEN 51.01 AND 100 THEN '51 - 100 Acres'
         WHEN ACREAGE BETWEEN 101 AND 200 THEN '101 - 200 Acres'
         WHEN ACREAGE BETWEEN 201 AND 300 THEN '201 - 300 Acres'         
         WHEN ACREAGE > 1000 THEN '1000 and Over'
         ELSE 'Invalid Acreage'
     END AS [Acreage Groups]
  FROM AcreageData
)
SELECT
[Acreage Groups],
COUNT([FIELD_ID]) as NumberofParcels   
FROM GroupAcreage
GROUP BY [Acreage Groups]
ORDER BY [Acreage Groups] ASC;

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。