Hi,
I'm working on the last question of a hw assignment in SQL, and for those that are familiar it utilizes the 'pubs' database in SQL Server. The question is:
"Change the ytd_sales (quantity) attribute in the titles table so that it contains the quantity for the books contained in the sales table. The two titles which have no sales should have a zero in their ytd_sales attribute. Assume here that the sales table contains year-to-date sales, even though the table's attributes refers to orders."
The issues here is that the 'quantity' value in the sales table, has multiple values per book, a la
Code:
title_id qty
-------- ---
abc 3
abc 5
def 4
ghi 1
ghi 7
Therefore, before the data can be updated into the titles table based on pk<->fk relationship around title_id the sales table needs to sum the qty and 'group' the title_id.
My plan was to do that, then put the result in a temporary table, then use those values from teh temp table to update the titles table.
The problem, is that when I run the code:
Code:
SELECT sum(qty),title_id
FROM Sales GROUP BY title_id
I get the expected results, but, when I run:
Code:
INSERT INTO tblTemp3(qty,title_id)
(SELECT sum(qty) as SumSales,title_id
FROM Sales GROUP BY title_id)
I get 'sequential' results, meaning that it really isn't grouping the data at all. Therefore, when I go to run:
Code:
UPDATE Titles
Set titles.ytd_sales=tblTemp3.SumSales
FROM titles JOIN tblTemp3 on titles.title_id=tblTemp3.title_id
I get invalid results.
Please help me troubleshoot this!
Thanks!
EDIT: Figured it out, thanks all!