Group by with update statement

Question

Post reply

Group by with update statement

afreenraja

SSC-Addicted

Points: 416
More actions
August 29, 2016 at 8:23 am

#316684

11 Points
115 Posts
Group by with update statement
24 minutes ago|LINK
Hi All,
Can anyone please help me to correct the below SQL update statement:
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
Thanks in advance.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

Looks like you've already asked elsewhere, but your query brings to mind some questions.

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.

Finally, you should read this article before submitting more questions. How To Post To Get The Best Help[/url]

__________________________________________________

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

afreenraja SSC-Addicted Points: 416 More actions · Answer 2

Thanks for your quick response.

Please find my answers:

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.

Phil Parkin SSC Guru Points: 246712 More actions · Answer 3

afreenraja (8/29/2016)
Thanks for your quick response.
Please find my answers:
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.

afreenraja SSC-Addicted Points: 416 More actions · Answer 4

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.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 5

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

afreenraja SSC-Addicted Points: 416 More actions · Answer 6

afreenraja

SSC-Addicted

Points: 416

August 29, 2016 at 9:59 am

#1898124

Thanks.

I will try these.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 7

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

Phil Parkin SSC Guru Points: 246712 More actions · Answer 8

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.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 9

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

afreenraja SSC-Addicted Points: 416 More actions · Answer 10

Yes first one worked for me.

Thanks alot for your help.