# Group by with update statement

• 11 Points

115 Posts

Group by with update statement

Hi All,

UPDATE T

SET SALE = SUM(Sale),

Exp = SUM(EXP)

FROM Sale T

INNER JOIN Exp S ON (T.Area = S.Area)

WHERE ordertype IN ('I')

GROUP BY Area

UPDATE T

SET SALE = SUM(Sale),

Exp = SUM(EXP)

FROM Sale T

INNER JOIN Exp S ON (T.Area = S.Area)

WHERE ordertype IN ('I')

GROUP BY Area

Which table does the ordertype column live in?

Are you summing the sale column from the sale table, or does Exp have a sale column too?

I assume the Exp value is from the Exp table. Is that correct?

Which area do you want to group by: S.area or T.area?

Is there a one to many relationship between the Sale and Exp tables?

Do you want the simple totals from both tables, or the totals as multiplied by the join?

Without answers, I can't code it for you, but you can sum up your totals in a cte or subquery, and then join the result set to the Sales Table to update it.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

• Thanks for your quick response.

Which table does the ordertype column live in? Exp

Are you summing the sale column from the sale table, or does Exp have a sale column too? Summing sale from Exp table.

I assume the Exp value is from the Exp table. Is that correct? Yes

Which area do you want to group by: S.area or T.area? S.Area

Is there a one to many relationship between the Sale and Exp tables? No

Do you want the simple totals from both tables, or the totals as multiplied by the join? Total only from Exp table.

Please let me know if you want any more clarifications.

• afreenraja (8/29/2016)

Which table does the ordertype column live in? Exp

Are you summing the sale column from the sale table, or does Exp have a sale column too? Summing sale from Exp table.

I assume the Exp value is from the Exp table. Is that correct? Yes

Which area do you want to group by: S.area or T.area? S.Area

Is there a one to many relationship between the Sale and Exp tables? No

Do you want the simple totals from both tables, or the totals as multiplied by the join? Total only from Exp table.

Please let me know if you want any more clarifications.

If the relationship between Sale and Exp is one-to-one, there is no need to do a SUM(). Are you sure about your answer?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

• I am sorry actually it has one-to-many relationship between Sale and Exp.

The main problem I am facing is using group by with update statement.

I hope it make sense to you.

Thanks again.

• Here are 2 options identical in execution. One uses a derived table and the other one uses a CTE.

`UPDATE T`

`SET SALE = S.Sale,`

` Exp = S.EXP`

`FROM Sale T`

`JOIN (SELECT Area,`

` SUM(Sale) AS Sale`

` SUM( EXP) AS EXP`

` FROM Exp`

` WHERE ordertype IN ('I')`

` GROUP BY Area) S ON (T.Area = S.Area);`

`WITH cteEXT AS(`

` SELECT Area,`

` SUM(Sale) AS Sale`

` SUM( EXP) AS EXP`

` FROM Exp`

` WHERE ordertype IN ('I')`

` GROUP BY Area`

`)`

`UPDATE T`

`SET SALE = S.Sale,`

` Exp = S.EXP`

`FROM Sale T`

`JOIN cteEXT S ON (T.Area = S.Area);`

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2
• Thanks.

I will try these.

• afreenraja (8/29/2016)

Thanks.

I will try these.

Try to understand them, so if there's an error you can correct it.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2
• Luis Cazares (8/29/2016)

afreenraja (8/29/2016)

Thanks.

I will try these.

Try to understand them, so if there's an error you can correct it.

There is more than one syntax error here (I thought you'd done it deliberately, as a learning exercise!)

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

• Phil Parkin (8/29/2016)

Luis Cazares (8/29/2016)

afreenraja (8/29/2016)

Thanks.

I will try these.

Try to understand them, so if there's an error you can correct it.

There is more than one syntax error here (I thought you'd done it deliberately, as a learning exercise!)

No, but I expected to have an error as I didn't test the code.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2
• Yes first one worked for me.