• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 142
  • Last Modified:

Help with update query containing aggregate

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
gbzhhu
Asked:
gbzhhu
  • 8
  • 5
  • 3
  • +1
2 Solutions
 
Phillip BurtonCommented:
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
 
gbzhhuAuthor Commented:
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
 
Phillip BurtonCommented:
But over what period? You need to be more specific for a specific answer.
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
gbzhhuAuthor Commented:
12 months.  I only need the last 12 months worth of data in the aggregated table

Thanks
0
 
Phillip BurtonCommented:
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
 
gbzhhuAuthor Commented:
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
 
awking00Commented:
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
 
gbzhhuAuthor Commented:
Thanks awking.  Will add that
0
 
Scott PletcherSenior DBACommented:
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
 
gbzhhuAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
awking00Commented:
Can you describe your salesDataAggregated table (i.e. column names and datatypes)?
0
 
awking00Commented:
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
 
awking00Commented:
Sorry, I left out needed commas here -
sda.SalesCount = q.SalesCount,
sda.SalesValue = q.SalesValue,
sda.modifiedDate = GETDATE()
0
 
gbzhhuAuthor Commented:
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
 
gbzhhuAuthor Commented:
Thank you all guys.  I will try this out on Monday.  It is late Friday for me :-)  Have a nice weekend y'all
0
 
awking00Commented:
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
 
gbzhhuAuthor Commented:
Not abandoned :-)  

Trying awking00 and ScottPletchers suggestions
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 8
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now