Results 1 to 6 of 6
  1. #1
    Join Date
    May 2011
    Posts
    3

    Unanswered: SQL Homework Help.

    Not looking for answers. But this is driving me nuts.

    #4 Write SQL SELECT statement that obtains the sum of QUANTITY_SOLD on
    1/16/2011 from the INVOICE table for the publisher whose publisher_id is 2.
    Hint: You will need to use an AND condition in the WHERE clause and also join the
    book table in this query to obtain the publisher_id (25%)
    Results:
    sum(invoice_line_item.quantity_sold)
    ------------------------------------
    92
    1 Row(s) affected

    Here is what I have so far.

    SELECT SUM(ILI.QUANTITY_SOLD)
    FROM INVOICE I
    JOIN INVOICE_LINE_ITEM ILI ON ILI.INVOICE_ID=I.INVOICE_ID
    JOIN BOOK B ON B.PUBLISHER_ID=B.PUBLISHER_ID
    JOIN PUBLISHER P ON I.INVOICE_ID=P.PUBLISHER_ID
    WHERE I.INVOICE_DATE='2011-1-16'

    My problem is when i run the sum, i get something like 140 instead of 92.
    When I take out my sum and run the query, i found that its counting some things more than once. its counting all of the books sold and then counting again of all the books by that publisher.

    I think I am lacking the understanding of Joins properly.

    What is the syntax of a join? maybe that would help me.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    looking at your requirement
    Write SQL SELECT statement that obtains the sum of QUANTITY_SOLD on
    1/16/2011 from the INVOICE table for the publisher whose publisher_id is 2.

    don't see that bit in your SQL
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your problem is right here --

    JOIN BOOK B ON B.PUBLISHER_ID=B.PUBLISHER_ID

    that's going to join every single row in the book table (except any books which don't have a publisher_id)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    May 2011
    Posts
    3
    I fixed the publisher id =2 after i posted. sorry.



    SELECT SUM(ILI.QUANTITY_SOLD)
    FROM INVOICE I
    JOIN INVOICE_LINE_ITEM ILI ON ILI.INVOICE_ID=I.INVOICE_ID
    JOIN BOOK B ON B.ISBN=B.ISBN
    JOIN PUBLISHER P ON P.PUBLISHER_ID=P.PUBLISHER_ID
    WHERE P.PUBLISHER_ID=2 AND INVOICE_DATE='2011-1-16'

    So I am working on invoice.

    Invoice has Invoice_ID and invoice_date.
    Inovice Line Item has invoice ID, ISBN and Quantity sold.
    Book has ISBN, TITLE, Subject ID, publisher ID, year published and price.
    Publisher has publisher ID and Publisher name.

    So I revamped it and this is where I am now.

    SELECT SUM(ILI.QUANTITY_SOLD)
    FROM INVOICE I
    JOIN INVOICE_LINE_ITEM ILI ON ILI.INVOICE_ID=I.INVOICE_ID
    JOIN BOOK B ON B.ISBN=B.ISBN
    WHERE B.PUBLISHER_ID=2 AND INVOICE_DATE='2011-1-16'

    Since I am working on invoice, and the quantity sold on invoice line item, I joined invoice line item in line 3. Also, since I need the publisher, I got that from book since book shares the isbn with invoice line item and also contains the publisher id. But do i need to link publisher since publisher ID is a foreign key to book?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this --

    JOIN BOOK B ON B.ISBN=B.ISBN

    will end up joining every single row in the book table (except any books which don't have an isbn)

    here's a tip: less haste, more speed

    look carefully at at the sql you have written, before you test it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    May 2011
    Posts
    3
    Bingo. I was linking the book wrong. Thanks! only one more assignment to do by midnight.

    SELECT SUM(ILI.QUANTITY_SOLD)
    FROM INVOICE I
    JOIN INVOICE_LINE_ITEM ILI ON ILI.INVOICE_ID=I.INVOICE_ID
    JOIN BOOK B ON ILI.ISBN=B.ISBN
    WHERE B.PUBLISHER_ID=2 AND INVOICE_DATE='2011-1-16'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •