在 Postgresql 中使用聚合和汇总

Question

我有一个简单的查询，旨在根据其他列的 GROUP BY 聚合来汇总 total_miles：

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

我在做什么这里错了？我基本上只想按等级汇总里程。

Answer 1

您按 study_type 进行分组，该列是基础表中每个层都有两个不同值的列。不过，您没有选择该列，而是使用常量“全部”。因此，您只需从 GROUP BY 子句中删除 study_type 列即可正常工作。

Answer 2

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