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'