CODEWITHSUNDEEP
pandaspostgresqldataframepandas-groupby
gwydion93
gwydion93

Reputation: 1923

Converting Python Groupby and aggregate to Postgres SQL

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?

Upvotes: 0

Views: 207

Answers (1)

Mose Wintner
Mose Wintner

Reputation: 308

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'

Upvotes: 1

Related Questions