Solved

Help with update query containing aggregate

Posted on 2015-01-16
20
110 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
ID: 40553256
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
ID: 40553268
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
ID: 40553274
But over what period? You need to be more specific for a specific answer.
0
 
LVL 12

Author Comment

by:gbzhhu
ID: 40553303
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
ID: 40553338
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
ID: 40553352
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 32

Expert Comment

by:awking00
ID: 40553448
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
ID: 40553471
Thanks awking.  Will add that
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40553675
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 12

Author Comment

by:gbzhhu
ID: 40553775
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
ID: 40553827
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 32

Expert Comment

by:awking00
ID: 40553856
Can you describe your salesDataAggregated table (i.e. column names and datatypes)?
0
 
LVL 32

Expert Comment

by:awking00
ID: 40553889
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 32

Expert Comment

by:awking00
ID: 40553892
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
ID: 40553937
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
ID: 40553944
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 32

Assisted Solution

by:awking00
awking00 earned 250 total points
ID: 40553963
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
ID: 40575004
Not abandoned :-)  

Trying awking00 and ScottPletchers suggestions
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

896 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

11 Experts available now in Live!

Get 1:1 Help Now