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
Go to Top of Page

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

Go to Top of Page

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 ]
Go to Top of Page

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?
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

Robert29b
Starting Member

6 Posts

Posted - 2014-09-12 : 15:29:55
yes, my database is SQLServerCompactedition
Go to Top of Page
   

- Advertisement -