none
An aggregate may not appear in the set list of an UPDATE statement.

    Question

  • Hi All,

    I know there are lot of examples to solve this one but i am unable to figure out the solution for my condition.Can someone please help me .The subject is the error and here is my update statement

    Update EDW.Selling.TEDW_R_Top_Report_Data
    Set [sales]=
    Case When   TEDW_S_TOP_REPORT_DATA.Group1 ='1-10'             THEN(SUM(TEDW_S_TOP_REPORT_DATA.sales)/10000)
    When TEDW_S_TOP_REPORT_DATA.Group1 ='11-30' THEN (SUM(TEDW_S_TOP_REPORT_DATA.sales)/10000)
    When TEDW_S_TOP_REPORT_DATA.Group1 ='31-60' THEN (SUM(TEDW_S_TOP_REPORT_DATA.sales)/10000)
    When TEDW_S_TOP_REPORT_DATA.Group1 ='61-100' THEN (SUM(TEDW_S_TOP_REPORT_DATA.sales)/10000)
    When TEDW_S_TOP_REPORT_DATA.Group1 ='100+' THEN (SUM(TEDW_S_TOP_REPORT_DATA.sales)/10000)
    END
    From edw.staging.TEDW_S_TOP_REPORT_DATA
    where TEDW_S_TOP_REPORT_DATA.Date_Level=TEDW_R_Top_Report_Data.Date_Level

    Monday, December 17, 2012 6:02 PM

Answers

  • I meant since you're grouping by several columns, your information should be unique for that columns combination.

    In my original re-write of your code I didn't have a JOIN. I am not sure why did you add it? If you want total sales based from one table, do the aggregate query first using only that table. The JOIN will come later.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, December 17, 2012 9:34 PM
    Moderator
  • You're joining based on the Date_Level column, but inside the subquery you're grouping by Group1, Date_Level, Transaction_Channel.

    You need to either join based on all these columns or don't include these columns into GROUP BY (and select list).

    Also, until you get your select statement correct don't try running the UPDATE. You need to make sure first that your SELECT is producing right data before you will be able to run the update.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, December 17, 2012 10:08 PM
    Moderator

All replies

  • The first step is to identify/define the cardinality of the relationship between the two tables.  So, for a given row in EDW.Selling.TEDW_R_Top_Report_Data, how many rows are related to it in the staging table?  1 or more than 1?  I suspect an important piece of information is missing from your description - perhaps related to the column Group1.  It would help to see a simple example of the data (both staging and selling) for at least two groups.
    Monday, December 17, 2012 7:08 PM
  • Hi Scott, if you see below i have staging and selling tables in the staging table i have individual sales where as for the selling i sum it up for 1-10.

    This is the selling table 

    Job_logging_key Transaction_Super_Channel Date_Level Prev_Current_Year Group_1 Group_2 Group_3 sales
    -1 RETAIL Year 2012 TY 61-100 31-100 Top 100 292
    -1 RETAIL Year 2012 TY 31-60 31-100 Top 100 292
    -1 RETAIL Year 2012 TY 11-30 Top 30 Top 100 290
    -1 RETAIL Year 2012 TY 1-10 Top 30 Top 100 302
    -1 RETAIL Year 2012 TY 100+ NULL NULL 1181

    This is the staging table

    Date_Level Channel  Rank Group1 Group2 Group3 sales
    Year 2012 RETAIL 1  11-30 Top 30 Top 100 2074639
    Year 2012 RETAIL 2 11-30 Top 30 Top 100 2032327
    Monday, December 17, 2012 7:13 PM
  • In short the sales in the staging table will be for items individually from 1-1000 where as in the selling table i will sum them up into groups as 1-10,11-30 and so on.

    If you need any more info please let me know.

    Thanks,

    Sujith.

    Monday, December 17, 2012 7:14 PM
  • HI SQL Novice,

    it still gives me the same error aggregate cannot be used in an update statement.

    Thanks,

    Sujith.

    Monday, December 17, 2012 7:16 PM
  • Hi Scott,

    To also let you know clearly for 10 rows in staging table i have 1 row in selling table with the sum of those 10.

    Thanks,

    Sujith.

    Monday, December 17, 2012 7:17 PM
  • Hi,

       Can you please post some sample data and the table structure? I'm not sure I understand the Table Definition and the data you've mentioned above.


    Please mark as answer if this answers your question. Please mark as helpful if you found this post helpful.

    Monday, December 17, 2012 7:34 PM
  • HI SQL Novice,

    I have 2 tables 1 is staging and the other one is selling.In the staging table i have lets say 10 rows for items 1-10 with individual sales but in the selling i only have 1 row for all these 10 with the sum of sales.I wrote an insert proc and it ran fine but now i also need an update proc such that if the staging and selling date matches then update the sales .

    If you have any questions please let me know.

    Thanks,

    Sujith.

    Monday, December 17, 2012 7:47 PM
  • I don't see the need for the CASE statement at all if you're using the same SUM expression.

    Also, which tables are used in that query? It looks like you're using two different tables and databases, right?

    I also suggest to move the first query into CTE (the aggregate query) and then update using that CTE.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, December 17, 2012 7:55 PM
    Moderator
  • HI Naomi,

    I am updating selling table from staging table.I guess i need the case statement because i have 5 groups like 1-10,11-30,31-60,61-100,100+ and i have to updated the sums of all the groups by taking the individual sales from staging table.

    Can you please help me with the CTE?i am new to sql so i am not sure of how to create a CTE.

    Thank you very much.

    Sujith.

    Monday, December 17, 2012 7:58 PM
  • Please post DDL for the tables - you can omit any "extra" columns that are not related to the issue.  It would also help to know how you would join the two tables to each other - is it really just on column DateLevel?  In addition, what exactly should happen with the current values in the Selling table - you appear to want to sum related rows from the staging table but what should be done with the existing value in the selling table?  Just overwrite it or include it in the computation?   Lastly, what do you want to happen when a row in the selling table has no associated rows in the staging table?

    Monday, December 17, 2012 8:05 PM
  • I think you need something like this:

    ;with cteGroupSales as (SELECT SUM(Sales) as TotalSales, Date_Level From edw.staging.TEDW_S_TOP_REPORT_DATA where Date_Level=TEDW_R_Top_Report_Data.Date_Level AND Group1 IN ('1-10','11-30','31-60','61-100','100+')

    GROUP BY Date_Level) SELECT * from cteGroupSales --UPDATE OT SET Sales = cte.TotalSales

    --FROM myOtherTable OT INNER JOIN cteGroupSales cte ON OT.Date_Level = cte.Date_Level

    For now I removed UPDATE statement and replaced it with SELECT so you should have a chance to review the total sales. I included the UPDATE statement (commented) below.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, December 17, 2012 8:11 PM
    Moderator
  • Hi Scott,

    here is the DDL for both the tables.

    USE [EDW]
    GO

    /****** Object:  Table [Staging].[TEDW_S_TOP_REPORT_DATA]    Script Date: 12/17/2012 15:12:15 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [Staging].[TEDW_S_TOP_REPORT_DATA](
    [Date_Level] [nvarchar](255) NULL,
    [Master_Silhouette_Item] [nvarchar](255) NULL,
    [Transaction_Channel] [nvarchar](255) NULL,
    [Rank] [nvarchar](255) NULL,
    [Group1] [nvarchar](255) NULL,
    [Group2] [nvarchar](255) NULL,
    [Group3] [nvarchar](255) NULL,
    [sales] [int] NULL,
    [units] [int] NULL,
    [GM] [int] NULL,
    [Date] [datetime] NULL
    ) ON [Fg_EDW_Staging_Data]

    GO

    USE [EDW]
    GO

    /****** Object:  Table [Selling].[TEDW_R_Top_Report_Data]    Script Date: 12/17/2012 15:12:36 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [Selling].[TEDW_R_Top_Report_Data](
    [Job_logging_key] [int] NOT NULL,
    [Transaction_Super_Channel] [nvarchar](255) NULL,
    [Date_Level] [nvarchar](255) NULL,
    [Prev_Current_Year] [nvarchar](255) NULL,
    [Group_1] [nvarchar](255) NULL,
    [Group_2] [nvarchar](255) NULL,
    [Group_3] [nvarchar](255) NULL,
    [sales] [int] NULL,
    [units] [int] NULL,
    [GM_Dollars] [int] NULL
    ) ON [Fg_EDW_Selling_Data]

    GO

    If you see the DDL both of these tables are pretty much the same .So we can join on any condition.And the existing values in the selling table must be over written with the new ones.If we dont have any match in staging and selling then insert but i already have an

    insert proc which is working fine.

    If you have any questions please let me know.

    Thank you very much.

    Sujith.

    Monday, December 17, 2012 8:13 PM
  • HI Naomi,

    If i just run the cte without update statement in this way

    ;with cteGroupSales as 
    (SELECT 
    SUM(TEDW_S_Top_Report_Data.sales) as TotalSales, TEDW_R_Top_Report_Data.Date_Level,TEDW_R_Top_Report_Data.Group_1,TEDW_R_Top_Report_Data.Transaction_Super_Channel
    From edw.staging.TEDW_S_TOP_REPORT_DATA
    left outer join edw.Selling.TEDW_R_Top_Report_Data ON TEDW_S_TOP_REPORT_DATA.Date_Level=TEDW_R_Top_Report_Data.Date_Level
    where TEDW_R_Top_Report_Data.Date_Level=TEDW_R_Top_Report_Data.Date_Level
    AND Group1 IN ('1-10','11-30','31-60','61-100','100+')
    GROUP BY TEDW_R_Top_Report_Data.Date_Level,TEDW_R_Top_Report_Data.Group_1,TEDW_R_Top_Report_Data.Transaction_Super_Channel)

    SELECT * from cteGroupSales

    The result set is 

    68105480 April - 2011 100+ DIRECT
    68105480 April - 2011 100+ Total
    68105480 April - 2011 1-10 DIRECT
    68105480 April - 2011 1-10 Total
    68105480 April - 2011 11-30 Total
    68105480 April - 2011 31-60 Total
    68105480 April - 2011 61-100 Total
    70015935 April - 2012 100+ DIRECT
    70015935 April - 2012 100+ Total
    70015935 April - 2012 1-10 DIRECT
    70015935 April - 2012 1-10 Total
    70015935 April - 2012 11-30 Total

    Which is giving the same sum for whole date level irrespective of groups and channel?Any changes i need to make.

    Thank you very much.

    Sujith.

    Monday, December 17, 2012 8:26 PM
  • Since you're grouping by Group_1 and Date_Level and Transaction_Super_Level, then SUM should be different for each of these column combinations.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, December 17, 2012 8:51 PM
    Moderator
  • HI Naomi,

    Can you be a little more clear?I exactly didnt understand what you said?Can you please help me with some explanation.

    Thanks,

    Sujith.

    Monday, December 17, 2012 8:54 PM
  • HI Naomi,

    I am trying to run this one

    UPDATE TEDW_R_Top_Report_Data
    SET [sales] = SumSales
    from EDW.Selling.TEDW_R_Top_Report_Data 
    left outer JOIN (Select 
    Date_Level,
    Group1,
    SUM(TEDW_S_TOP_REPORT_DATA.sales)/100000 AS SumSales,
    Transaction_Channel
    from edw.staging.TEDW_S_TOP_REPORT_DATA
    group by Date_Level,
    TEDW_S_TOP_REPORT_DATA.Group1, 
    TEDW_S_TOP_REPORT_DATA.Transaction_Channel
    )  AS TEDW_S_TOP_REPORT_DATA ON TEDW_R_Top_Report_Data.Date_Level=TEDW_S_TOP_REPORT_DATA.Date_Level
    where TEDW_R_Top_Report_Data.Date_Level=TEDW_S_TOP_REPORT_DATA.Date_Level

    If i use the select statement inside it gives correct value but if execute everything it gives wrong values.Can you please help me if i did something wrong with joins or anything?

    Thanks,

    Sujith

    Monday, December 17, 2012 9:29 PM
  • I meant since you're grouping by several columns, your information should be unique for that columns combination.

    In my original re-write of your code I didn't have a JOIN. I am not sure why did you add it? If you want total sales based from one table, do the aggregate query first using only that table. The JOIN will come later.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, December 17, 2012 9:34 PM
    Moderator
  • You're joining based on the Date_Level column, but inside the subquery you're grouping by Group1, Date_Level, Transaction_Channel.

    You need to either join based on all these columns or don't include these columns into GROUP BY (and select list).

    Also, until you get your select statement correct don't try running the UPDATE. You need to make sure first that your SELECT is producing right data before you will be able to run the update.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, December 17, 2012 10:08 PM
    Moderator