help with sum from one table and write it to anoth

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

help with sum from one table and write it to anoth

Author

Topic

Robert29b
Starting Member

6 Posts

Posted - 2014-09-12 : 13:02:55

table 1 = customer | primary-key = customer.sid
table 2 = orders | foreign-key = orders.customer_sid

I would like to sum orders.sale_amount where customer.sid = orders.customer_sid and update customer.sales_total.

I am new to SQl and here was my original statement (which doesn't work)

UPDATE customer
inner JOIN orders ON (customer.sid = orders.customer_Sid)
SET customer.sales_total = SUM(orders.sale_amount)
GROUP BY customer.sid;

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-12 : 13:31:38

You need to put the aggregation in a subquery:


UPDATE customer
SET sales_total = aggr.sales_total
FROM customer
INNER JOIN (
    SELECT sid
        ,sum(sales_amount) sales_total
    FROM customer
    INNER JOIN orders
        ON (customer.sid = orders.customer_Sid)
    GROUP BY customer.sid
    ) aggr
    ON customer.sid = aggr.sid;

ALso note that the SET command comes right after the UPDATE command

Robert29b
Starting Member

6 Posts

Posted - 2014-09-12 : 13:40:06

quote:

Originally posted by gbritton

You need to put the aggregation in a subquery:


UPDATE customer
SET sales_total = aggr.sales_total
FROM customer
INNER JOIN (
    SELECT sid
        ,sum(sales_amount) sales_total
    FROM customer
    INNER JOIN orders
        ON (customer.sid = orders.customer_Sid)
    GROUP BY customer.sid
    ) aggr
    ON customer.sid = aggr.sid;

ALso note that the SET command comes right after the UPDATE command

Robert29b
Starting Member

6 Posts

Posted - 2014-09-12 : 13:40:23

Major Error 0x80040E14, Minor Error 25501
> UPDATE customer
SET sales_total = aggr.sales_total
FROM customer
INNER JOIN (
SELECT sid
,sum(sales_amount) sales_total
FROM customer
INNER JOIN orders
ON (customer.sid = orders.customer_Sid)
GROUP BY customer.sid
) aggr
ON customer.sid = aggr.sid
There was an error parsing the query. [ Token line number = 3,Token line offset = 1,Token in error = FROM ]

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-12 : 14:09:54

It parses and runs on my database.

BTW I've never seen those Major Error/Minor Error things. Is that some SSMS Plugin you're using?

BTW did you try to run it?

Robert29b
Starting Member

6 Posts

Posted - 2014-09-12 : 14:14:58

I ran it on SQL server 2008 r2 and my database is just a local db.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-12 : 14:20:42

I just ran this exact query (I had to use the db schema batch) on my 2008r2 instance without error:


CREATE TABLE batch.customer (
    sid INT
    ,sales_total MONEY
    )

CREATE TABLE batch.orders (
    customer_sid INT
    ,sales_amount MONEY
    )

INSERT INTO batch.customer (sid)
VALUES (1)

INSERT INTO batch.orders (
    customer_sid
    ,sales_amount
    )
VALUES (
    1
    ,10.0
    )


UPDATE customer
SET sales_total = aggr.sales_total
FROM batch.customer customer
INNER JOIN (
    SELECT sid
        ,sum(sales_amount) sales_total
    FROM batch.customer customer
    INNER JOIN batch.orders
        ON (customer.sid = orders.customer_Sid)
    GROUP BY customer.sid
    ) aggr
    ON customer.sid = aggr.sid;

SELECT * FROM BATCH.CUSTOMER

it produced:


sid	sales_total
1	10.00

what is the tool you used that throws the messages:

quote:

Major Error 0x80040E14, Minor Error 25501
There was an error parsing the query. [ Token line number = 3,Token line offset = 1,Token in error = FROM ]

I do not think that those are from SSMS

Robert29b
Starting Member

6 Posts

Posted - 2014-09-12 : 14:25:30

SQL Server management Studio 2008 R2

Microsoft SQL Server Management Studio 10.50.2500.0
Microsoft Data Access Components (MDAC) 6.1.7601.17514
Microsoft MSXML 3.0 6.0
Microsoft Internet Explorer 9.0.8112.16421
Microsoft .NET Framework 2.0.50727.5485
Operating System 6.1.7601

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-12 : 14:33:08

Are you are using SQL Server CE edition? SQL Server Compact Edition actually doesn't perform very well with SQL queries.SQL Server Compact Edition only supports a subset of Transact-SQL commands.

SSMS on other editions of SQL Server do not have this problem.

Try it this way:


UPDATE Customer
SET sales_total = aggr.sales_total
FROM (
    SELECT sid
        ,sum(sales_amount) sales_total
    FROM customer customer
    INNER JOIN orders
        ON (customer.sid = orders.customer_Sid)
    GROUP BY customer.sid
    ) aggr
WHERE customer.sid = aggr.sid

Robert29b
Starting Member

6 Posts

Posted - 2014-09-12 : 15:29:55

yes, my database is SQLServerCompactedition

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources