Using Aggregation and Summarization in Postgresql-CodePudding

I have a simple query that is designed to summarize total_miles based on a GROUP BY aggregation of other columns:

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

Currently, it outputs:

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

I want it to output:

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

What am I doing wrong here? I basically want the miles summarized on tier only.

CodePudding user response：

You're grouping by study_type, which is a column that has two distinct values per each tier in your underlying table. You're not selecting that column though, you use a constant 'All' as that. So you simply have to remove the study_type column from your GROUP BY clause and it'll work just fine.

CodePudding user response：

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