Home > Net >  Converting Python Groupby and aggregate to Postgres SQL
Converting Python Groupby and aggregate to Postgres SQL

Time:10-13

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?

CodePudding user response:

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'
  • Related