Solved

Help with update query containing aggregate

Posted on 2015-01-16
20
109 Views
Last Modified: 2015-05-23
Hi,

I have the following query which fails with error - An aggregate may not appear in the set list of an UPDATE statement.  Why can't I update a table with aggregated data from another table?

      -- Update
      UPDATE sda
      SET monthCount = SUM(finalQty),
            monthValue = SUM(finalValue),
            modifiedDate = GETDATE()
      FROM salesData sd
            INNER JOIN customer c ON c.efssNumber = sd.efssCustomerNumber AND c.number = sd.customerNumber
            INNER JOIN product p ON p.productNumber = sd.productNumber
            INNER JOIN salesDataAggregated sda ON sda.customerRef = c.id AND sda.productRef = p.id AND sda.[month] = MONTH(sd.orderDate)
0
Comment
Question by:gbzhhu
  • 8
  • 5
  • 3
  • +1
20 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
It doesn't make sense. It seems you are trying to update all of the rows for the columns monthCount, monthValue and modifiedDate with the same information.

What are you trying to do?
0
 
LVL 12

Author Comment

by:gbzhhu
Comment Utility
Hi Phillip,

salesData has all sales data history and is added to all the time.  I have salesDataAggregated which I want to keep aggregated sales data for the past 12 months.  I am trying to write a stored procedure to run every morning to update/insert into salesDataAggregated from salesData table.

I can provide the schemas for the 2 tables if it will help much

Thanks
0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
But over what period? You need to be more specific for a specific answer.
0
 
LVL 12

Author Comment

by:gbzhhu
Comment Utility
12 months.  I only need the last 12 months worth of data in the aggregated table

Thanks
0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
So,

>> I want to keep aggregated sales data for the past 12 months

You only want one row in that table? Be more specific.
0
 
LVL 12

Author Comment

by:gbzhhu
Comment Utility
For a given customer and product I would like one row per month for the past 12 months in the aggregated table.  If no data exists for that month then there will be no row (ideally).  So

c.id     p.id       month       countTotal       valueTotal  ModifiedDate
=========================================================
12      20          3                 10                       1,245.23    Somedate
12      20          6                 3                         25.23          Somedate
12      20          11               130                     4,245.23    Somedate
12      35          1                 6                         1,75.23       Somedate
12      35          2                 1                         4.23            Somedate
12      120        3                 25                       745.23       Somedate
12      11          3                 18                       988.23       Somedate
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
Some sample data and expected results with an explanation of the criteria used to get those results would help the experts greatly in offering a solution to your issue.
0
 
LVL 12

Author Comment

by:gbzhhu
Comment Utility
Thanks awking.  Will add that
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Should be something very close to below.  I guess the "salesData" table only holds data for a rolling 12 months, since no provision for different date years was in the code.


UPDATE sda
      SET monthCount = SUM(finalQty),
          monthValue = SUM(finalValue),
          modifiedDate = GETDATE()
FROM salesDataAggregated sda
INNER JOIN (
       SELECT c.id AS c_id, p.id AS p_id, MONTH(sd.orderDate) AS orderDate_month,
             SUM(finalQty) AS sum_finalQty, SUM(finalValue) AS sum_finalValue
       FROM salesData sd
             INNER JOIN customer c ON c.efssNumber = sd.efssCustomerNumber AND c.number = sd.customerNumber
             INNER JOIN product p ON p.productNumber = sd.productNumber
       GROUP BY
             c.id, p.id, MONTH(sd.orderDate)
) AS tots on sda.customerRef = tots.c_id AND
             sda.productRef = tots.p_id AND
             sda.[month] = tots.orderDate_month
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 12

Author Comment

by:gbzhhu
Comment Utility
If you look in the source data result below for customerId of 2 and productId of 61 with orderDate which has March in month you will see 2 rows with item count of 1 each and cost of 61.34 each.  But of you look at the query result below for the same data there will be row item count of 2 and total cost of 122.68 which shows the aggregation is working.  I am having trouble writing the update query to insert into my aggregatedData table.  I just started this and it is not my strongest area so any general help would be useful

-Source data

CustomerId      orderDate      ProductId      finalQty      finalValue
2      11/09/2014      22      8      1097.44
2      22/09/2014      22      -8      -1097.44
2      11/04/2014      62      1      63.5
2      08/10/2014      62      1      63.5
2      22/12/2014      62      1      63.5
2      06/01/2014      62      1      63.4
2      27/08/2014      62      1      63.5
2      27/08/2014      305      2      25.82
2      08/10/2014      595      2      59.18
2      17/10/2014      569      1      80
2      21/02/2014      569      1      80
2      27/08/2014      569      1      80
2      08/10/2014      61      2      122.68
2      22/12/2014      61      2      122.68
2      06/01/2014      61      1      58.98
2      20/03/2014      61      1      61.34
2      26/03/2014      61      1      61.34
2      11/04/2014      61      1      61.34
2      17/04/2014      61      2      122.68
2      28/08/2014      531      10      1250
2      18/12/2013      531      6      854.7
2      22/04/2014      531      10      1250
2      23/01/2014      94      2      49.24



-QUERY to generate aggrgated data from the source data

SELECT TOP 23 c.id [Customer],
         p.id [Product],
         MONTH(orderDate) [Month],
       SUM(finalQty) [SalesCount],
       SUM(finalValue) [SalesValue],
       sd.modifiedDate
FROM salesData sd
      INNER JOIN customer c ON c.efssNumber = sd.efssCustomerNumber AND c.number = sd.customerNumber
      INNER JOIN product p ON p.productNumber = sd.productNumber
WHERE orderDate BETWEEN DATEADD(m,-12,GETDATE()) AND GETDATE()
GROUP BY c.id, p.id, MONTH(orderDate), sd.modifiedDate
ORDER BY c.id, p.id, MONTH(orderDate)

-QUERY RESULT
c.id      p.id      month      salesCount      salesValue      modifiedDate
2      22      9      0      0      07/01/2015
2      61      3      2      122.68      07/01/2015
2      61      4      3      184.02      07/01/2015
2      61      10      2      122.68      07/01/2015
2      61      12      2      122.68      07/01/2015
2      62      4      1      63.5      07/01/2015
2      62      8      1      63.5      07/01/2015
2      62      10      1      63.5      07/01/2015
2      62      12      1      63.5      07/01/2015
2      94      1      2      49.24      07/01/2015
2      305      8      2      25.82      07/01/2015
2      531      4      10      1250      07/01/2015
2      531      8      10      1250      07/01/2015
2      569      2      1      80      07/01/2015
2      569      8      1      80      07/01/2015
2      569      10      1      80      07/01/2015
2      595      10      2      59.18      07/01/2015
4      3      5      3      474      07/01/2015
4      12      2      0      -6.35      07/01/2015
4      12      10      3      73.41      07/01/2015
4      18      2      0      -6.32      07/01/2015
4      18      8      2      109.7      07/01/2015
4      20      4      1      142.34      07/01/2015
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 250 total points
Comment Utility
UPDATE sda
       SET monthCount = sum_finalQty,
           monthValue = sum_finalValue,
           modifiedDate = GETDATE()
 FROM salesDataAggregated sda
 INNER JOIN (
        SELECT c.id AS c_id, p.id AS p_id, DATEADD(MONTH, DATEDIFF(MONTH, 0, sd.orderDate), 0) AS orderDate_month,
              SUM(finalQty) AS sum_finalQty, SUM(finalValue) AS sum_finalValue
        FROM salesData sd
              INNER JOIN customer c ON c.efssNumber = sd.efssCustomerNumber AND c.number = sd.customerNumber
              INNER JOIN product p ON p.productNumber = sd.productNumber
        WHERE sd.orderDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 11, 0) AND
              sd.orderDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)
        GROUP BY
              c.id, p.id, DATEADD(MONTH, DATEDIFF(MONTH, 0, sd.orderDate), 0)
 ) AS tots on sda.customerRef = tots.c_id AND
              sda.productRef = tots.p_id AND
              sda.[month] = tots.orderDate_month
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
Can you describe your salesDataAggregated table (i.e. column names and datatypes)?
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
You may want to consider using a merge, something like this -
merge into salesDataAggregated sda
using
(your query results) q
on
(sda.customer = q.cid and sda.product = q.pid)
when matched then
update set
sda.month = q.month,
sda.SalesCount = q.SalesCount
sda.SalesValue = q.SalesValue
sda.modifiedDate = GETDATE()
;

Not sure I have the right column names for your query, but you get the idea. Also note that if new customers or products are introduced, you can do an insert when not matched with the merge operation.
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
Sorry, I left out needed commas here -
sda.SalesCount = q.SalesCount,
sda.SalesValue = q.SalesValue,
sda.modifiedDate = GETDATE()
0
 
LVL 12

Author Comment

by:gbzhhu
Comment Utility
Here is the script that creates the salesDataAggregated table


CREATE TABLE [dbo].[salesDataAggregated](
      [customerRef] [int] NOT NULL,
      [productRef] [int] NOT NULL,
      [month] [int] NOT NULL,
      [monthCount] [int] NULL,
      [monthValue] [decimal](18, 2) NULL,
      [modifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_salesDataAggregated_2] PRIMARY KEY CLUSTERED
(
      [customerRef] ASC,
      [productRef] ASC,
      [month] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[salesDataAggregated]  WITH CHECK ADD  CONSTRAINT [FK_salesDataAggregated2_customer] FOREIGN KEY([customerRef])
REFERENCES [dbo].[customer] ([id])
GO

ALTER TABLE [dbo].[salesDataAggregated] CHECK CONSTRAINT [FK_salesDataAggregated2_customer]
GO

ALTER TABLE [dbo].[salesDataAggregated]  WITH CHECK ADD  CONSTRAINT [FK_salesDataAggregated2_product] FOREIGN KEY([productRef])
REFERENCES [dbo].[product] ([id])
GO

ALTER TABLE [dbo].[salesDataAggregated] CHECK CONSTRAINT [FK_salesDataAggregated2_product]
GO

ALTER TABLE [dbo].[salesDataAggregated] ADD  CONSTRAINT [DF_salesDataAggregated2_modifiedDate]  DEFAULT (getdate()) FOR [modifiedDate]
GO
0
 
LVL 12

Author Comment

by:gbzhhu
Comment Utility
Thank you all guys.  I will try this out on Monday.  It is late Friday for me :-)  Have a nice weekend y'all
0
 
LVL 31

Assisted Solution

by:awking00
awking00 earned 250 total points
Comment Utility
If month is part of the key, then the merge statement should look something like this -
merge into salesDataAggregated sda
using
(your query results) q
on
(sda.customerRef = q.cid and sda.productRef = q.pid and sda.month = q.month)
when matched then
update set
sda.SalesCount = q.SalesCount,
sda.SalesValue = q.SalesValue,
sda.modifiedDate = GETDATE()
;
0
 
LVL 12

Author Comment

by:gbzhhu
Comment Utility
Not abandoned :-)  

Trying awking00 and ScottPletchers suggestions
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now