Group by with update statement

  • 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.

  • 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

  • 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.

  • 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

  • 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!)

    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

  • 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.

    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