一个字段的Oracle SQL计数由另一个字段分组

Matt 发表于 Dev

马特

我有一个小查询，在该查询下，我希望每个都有一个计数HOUSE_ID和分组依据，LOCATION_ID但是LOCATION_ID由于的HOUSE_ID不同，因此没有分组依据。我想它来计算HOUSE_ID的通过LOCATION_ID的不管HOUSE_ID。

询问

SELECT 
COUNT(HOUSE_ID) AS Count,
LOCATION_ID,
ZONE,
AREA
FROM TABLE
WHERE SITE_ID = 'ABC'
AND LOCATION_ID NOT LIKE ('%LAND%')
GROUP BY LOCATION_ID, HOUSE_ID, ZONE, AREA

预期结果

_____________________________
|Count|LOCATION_ID|ZONE|AREA|
|¯¯¯¯¯|¯¯¯¯¯¯¯¯¯¯¯|¯¯¯¯|¯¯¯¯|
|  4  |  LOCA     | 2  |  1 |
|  7  |  LOCB     | 6  |  2 |
|  3  |  LOCC     | 3  |  1 |
|  9  |  LOCD     | 5  |  7 |
|  6  |  LOCE     | 7  |  4 |
|  2  |  LOCF     | 2  |  1 |
|  8  |  LOCG     | 7  |  5 |
|  7  |  LOCH     | 9  |  1 |
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯

实际结果

_____________________________
|Count|LOCATION_ID|ZONE|AREA|
|¯¯¯¯¯|¯¯¯¯¯¯¯¯¯¯¯|¯¯¯¯|¯¯¯¯|
|  1  |  LOCA     | 2  |  1 |
|  1  |  LOCA     | 6  |  2 |
|  1  |  LOCA     | 3  |  1 |
|  1  |  LOCA     | 5  |  7 |
|  1  |  LOCA     | 7  |  4 |
|  1  |  LOCA     | 2  |  1 |
|  1  |  LOCA     | 7  |  5 |
|  1  |  LOCA     | 9  |  1 |
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯

马特

知道了，需要数（*）！

SELECT 
COUNT(*) AS Count,
SUM(AREA) AS AREA
LOCATION_ID,
ZONE,
FROM TABLE
WHERE SITE_ID = 'ABC'
AND LOCATION_ID NOT LIKE ('%LAND%')
GROUP BY LOCATION_ID, ZONE

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

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