MySQL SUM(GROUP BY)

Question

I am trying to add up columns where the ID are the same. I'm new to MySQL but it appears that Group By is what I need.

Here is my attempt but it gives me a syntax error

|SiteID|StaffID|Holiday|Total

Update SiteStaff
SET Total = SUM(h.Holiday) 
GROUP BY h.StaffID

see this post. you cant directly use group by stackoverflow.com/questions/6898935/… — Anonymous Duck
– Anonymous Duck, Commented Jun 1, 2015 at 9:52
It had it in an example i looked at. I assumed it was a group name — Mantis
– Mantis, Commented Jun 1, 2015 at 10:23

Gordon Linoff · Accepted Answer · 2015-06-01 11:15:54Z

1

I suspect you have a holiday table. If so, you want a query that uses join with update, and looks something like this:

Update SiteStaff ss LEFT JOIN
       (SELECT h.StaffId, SUM(h.holiday) as total
        FROM Holidays h
        GROUP BY h.StaffID
       ) h
       ON ss.StaffId = h.StaffId
    SET ss.Total = COALESCE(t.total, 0);

answered Jun 1, 2015 at 11:15

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gopal Kildoliya · Accepted Answer · 2015-06-01 10:00:16Z

0

I think there may be following reasons of the error:-

You have used h.Holidayin your code. So what is h here ?
Second reason may be you have to use separate code to find the SUM first and the UPDATE the table.

answered Jun 1, 2015 at 10:00

Gopal Kildoliya

6495 silver badges21 bronze badges

Comments

Mantis · Accepted Answer · 2015-06-01 10:55:08Z

0

Worked it out from looking at more examples

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

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

answered Jun 1, 2015 at 10:55

Mantis

1,4074 gold badges28 silver badges53 bronze badges

Collectives™ on Stack Overflow

MySQL SUM(GROUP BY)

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related