Need quick SQL help

Discussion in 'OT Technology' started by EdvardGrieg, Aug 3, 2005.

  1. EdvardGrieg

    EdvardGrieg New Member

    Joined:
    Oct 17, 2004
    Messages:
    62
    Likes Received:
    0
    Location:
    Denver, CO
    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!
     
  2. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    if i understand this right, this might be what you need:

    Code:
    
    UPDATE Titles
    SET titles.ytd_sales = b.SumSales
    FROM (SELECT sum(qty) as SumSales, title_id
           FROM Sales 
           GROUP BY title_id ) b
       JOIN Titles t ON t.title_id = b.title_id
    
    EDIT: Damn code block doesn't preserve tab formatting. You also don't need the temp table if you do this with a subSelect like i did.
     
  3. EdvardGrieg

    EdvardGrieg New Member

    Joined:
    Oct 17, 2004
    Messages:
    62
    Likes Received:
    0
    Location:
    Denver, CO
    Awesome! Thanks SLED! I had originally tried a subSelect, but didn't name it as you did, which is why I think it didn't work.
     
  4. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!

Share This Page