Tilted Forum Project Discussion Community

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!