Using Aggregation and Summarization in Postgresql

Question

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.

MarcinJ · Accepted Answer · 2022-03-21 21:57:33Z

1

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.

answered Mar 21, 2022 at 21:57

MarcinJ

3,6592 gold badges16 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

gwydion93 Over a year ago

Yes; that was the case! It works now, thanks.

Chowlett2 · Accepted Answer · 2022-03-21 22:11:01Z

0

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

answered Mar 21, 2022 at 22:11

Chowlett2

1711 gold badge1 silver badge8 bronze badges

1 Comment

the Tin Man Over a year ago

See "Explaining entirely code-based answers". While this might be technically correct, it doesn't explain why it solves the problem or should be the selected answer. We should educate along with helping solve the problem.

Collectives™ on Stack Overflow

Using Aggregation and Summarization in Postgresql

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related