Converting Python Groupby and aggregate to Postgres SQL

Question

Suppose I have a table called cnms_table in my PostgresSQL database that is equivalent to a pandas dataframe called cnms_df that I created in a Python script. In the Python dataframe, I was able to use groupby and agg to summarize and aggregate the dataframe based on specific columns/fields to create a new summarized dataframe called sum_df.

 sum_df_prelim = cnms_df.groupby(['Region', 'State',  'CO_FIPS', 'Tiermetric_Prelim',
                              'Mod_unMod_Prelim', 'Val_Combined_Prelim', 'Det_Approx_Prelim',
                              'Decay_Date_Prelim'], as_index=False).agg({'Actual_Miles_Prelim': 'sum'})

How would I do this in a Postgres SQL query or function?

Mose Wintner · Accepted Answer · 2021-10-11 16:13:47Z

1

SELECT
  SUM(Actual_Miles_Prelim)
FROM
  cnms_table
GROUP BY
  'Region',
  'State',
  'CO_FIPS',
  'Tiermetric_Prelim',
  'Mod_unMod_Prelim',
  'Val_Combined_Prelim', 
  'Det_Approx_Prelim',
  'Decay_Date_Prelim'

answered Oct 11, 2021 at 16:13

Mose Wintner

3081 silver badge10 bronze badges

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

1 Comment

gwydion93 Over a year ago

This worked but only after I removed the ' 's from the column names. Thanks.

Collectives™ on Stack Overflow

Converting Python Groupby and aggregate to Postgres SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related