我有一個簡單的查詢，旨在total_miles根據GROUP BY其他列的聚合進行匯總：
SELECT
tiermetric AS tier,
st AS state,
validation,
'All' AS study_type,
SUM(total_miles) as total_miles
FROM mu_schema.my_table
WHERE validation = 'VALID'
AND st = 'VA'
GROUP BY
tiermetric,
state,
validation,
study_type
ORDER BY tiermetric
目前，它輸出：
tier state validation study_type total_miles
TIER 2 VA VALID All 335.005721465211
TIER 2 VA VALID All 0.337142821958635
TIER 3 VA VALID All 13.3444415576409
TIER 3 VA VALID All 1651.56942736755
TIER 4 VA VALID All 6606.44868771768
TIER 4 VA VALID All 1399.72188798074
我希望它輸出：
tier state validation study_type total_miles
TIER 2 VA VALID All 335.3421
TIER 3 VA VALID All 1664.9138
TIER 4 VA VALID All 8006.1704
我在這里做錯了什么？我基本上想要僅在等級上匯總的里程。
uj5u.com熱心網友回復：
您按 分組study_type，該列在基礎表中的每一層都有兩個不同的值。但是，您沒有選擇該列，而是使用常量“全部”。因此，您只需從子句中洗掉該study_type列，它就可以正常作業。GROUP BY
uj5u.com熱心網友回復：
SELECT
tiermetric AS tier,
st AS state,
validation,
'All' AS study_type,
SUM(total_miles) as total_miles
FROM mu_schema.my_table
WHERE validation = 'VALID'
AND st = 'VA'
GROUP BY
tiermetric,
state,
validation
ORDER BY tiermetric
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/446047.html
標籤：PostgreSQL 聚合 概括