[SQL] Need quick help - Tilted Forum Project Discussion Community

Edvard_Grieg · 08-03-2005, 09:14 AM

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!

08-03-2005, 09:14 AM	#1 (permalink)
Edvard_Grieg Upright Location: Denver	[SQL] Need quick help 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! __________________ Do be do be do Last edited by Edvard_Grieg; 08-03-2005 at 09:33 AM..