I trying to do 2 queries with different table, union them and then do a groupby with sum. Here is the PostgreSQL code snippet:
SELECT
to_char(worklist.date,'YYYY-MM') AS year_month,
department.department_name AS department_name,
user.username AS user_name,
SUM(worklist.hour) AS TotalHour,
SUM(worklist.overtime_hour) AS overtime_TotalHour,
CAST(NULL AS numeric(20)) AS total_price
FROM user, worklist, department
WHERE user.id = worklist.user_id AND
user.department_id = department.id
group BY year_month, user_name, department_name
UNION
SELECT
to_char(expenditure.date,'YYYY-MM') AS year_month,
department.department_name AS department_name,
user.username AS user_name,
CAST(NULL AS numeric(20)) AS TotalHour,
CAST(NULL AS numeric(20)) AS overtime_TotalHour,
SUM(expenditure.price) AS total_price
FROM user, department, expenditure
WHERE user.id = expenditure.user_id AND
user.department_id = department.id
group BY year_month, user_name, department_name
ORDER BY year_month DESC
query result: PostgreSQL query result
I want to implement this in sqlalchemy and here is how i would approach it:
def get_allusers_monthly(db: Session):
qry1 = (db.query(
func.to_char(workhour.Workhour.date,'YYYY-MM').label('year_month'),
department.Department.department_name.label('department_name'),
user.User.username.label('user_name'),
func.sum(workhour.Workhour.hour).label('total_hour'),
func.sum(workhour.Workhour.overtime_hour).label('total_overtime_hour'),
cast(expen.Expenditure.price, Numeric(10)).label('total_pric')
).filter(user.User.id == workhour.Workhour.user_id and user.User.department_id == department.Department.id
).group_by(
'year_month',
'department_name',
'user_name',
'total_pric'
).all())
qry2 = (db.query(
func.to_char(expen.Expenditure.date,'YYYY-MM').label('year_month'),
department.Department.department_name.label('department_name'),
user.User.username.label('user_name'),
cast(workhour.Workhour.hour, Numeric(10)).label('total_hour'),
cast(workhour.Workhour.overtime_hour, Numeric(10)).label('total_overtime_hour'),
func.sum(expen.Expenditure.price).label('total_pric')
).filter(user.User.id == expen.Expenditure.user_id and user.User.department_id == department.Department.id
).group_by(
'year_month',
'department_name',
'user_name',
'total_hour',
'total_overtime_hour'
).all())
all_queries = [qry1, qry2]
golden_set = union(*all_queries).subquery()
return golden_set
Here is the outputted that gets this error:
sqlalchemy.exc.ArgumentError: SELECT construct for inclusion in a UNION or other set construct expected
Where am I wrong? or is there has any better way to implement this
Can anybody please help?!