[SQL] Need quick help

[Edvard_Grieg]

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

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:

SELECT sum(qty),title_id
FROM Sales GROUP BY title_id

I get the expected results, but, when I run:

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:

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!