CODEWITHSUNDEEP
pythonmysqlsqlalchemy
Mantis
Mantis

Reputation: 1387

sqlalchemy group_by and sum

I have a SiteStaff table that I want to group all the staff together with the same staffId and add up the holiday column.

>     group =  session.query(SiteStaff, func.sum(SiteStaff.Holiday)).group_by(SiteStaff.StaffID).all()
>     
>     print group

The output groups the staff together but does not add the column.

Here is the sql that i am trying to mimic:

UPDATE Staff p, (SELECT StaffID, SUM(Holiday) as mysum
 FROM SiteStaff GROUP BY StaffID) as s

    SET p.TotalDaysHolidayAllowed = s.mysum
    WHERE p.StaffID = s.StaffID

Upvotes: 3

Views: 2728

Answers (1)

Mantis
Mantis

Reputation: 1387

It was the StaffSite part that stopped it from working. I needed to add each field in like so:

session.query(SiteStaff.StaffID, func.sum(SiteStaff.Holiday)).group_by(SiteStaff.StaffID).all()

Upvotes: 4

Related Questions