group by date_permittd year, week, job_type., occ_category on units_net
SELECT Extract('year' FROM date_permittd :: timestamp) AS year,
Extract('week' FROM date_permittd :: timestamp) AS week,
job_type,
occ_category,
SUM(units_net :: NUMERIC)
FROM devdb_export
GROUP BY Extract('year' FROM date_permittd :: timestamp),
Extract('week' FROM date_permittd :: timestamp),
job_type,
occ_category
from sqlalchemy import create_engine
import pandas as pd
conn = create_engine('postgresql://XXX:XXX@XXX:XXX/XXX')
df_permitted = pd.read_sql('''
SELECT Extract('year' FROM date_permittd :: timestamp) AS year,
Extract('week' FROM date_permittd :: timestamp) AS week,
job_type,
occ_category,
SUM(units_net :: NUMERIC)
FROM devdb_export
GROUP BY Extract('year' FROM date_permittd :: timestamp),
Extract('week' FROM date_permittd :: timestamp),
job_type,
occ_category
''', con = conn)
group by date_permittd year, week, job_type., occ_category on units_net