August 29, 2016 at 8:23 am
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.
August 29, 2016 at 8:45 am
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
August 29, 2016 at 8:57 am
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.
August 29, 2016 at 9:14 am
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?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 29, 2016 at 9:20 am
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.
August 29, 2016 at 9:42 am
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);
August 29, 2016 at 9:59 am
Thanks.
I will try these.
August 29, 2016 at 10:07 am
afreenraja (8/29/2016)
Thanks.I will try these.
Try to understand them, so if there's an error you can correct it.
August 29, 2016 at 10:23 am
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!)
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 29, 2016 at 10:28 am
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.
August 29, 2016 at 11:33 am
Yes first one worked for me.
Thanks alot for your help.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply