SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    I want my 4th arrow! garlinto's Avatar
    Join Date
    Jun 2002
    Location
    Riding the electron wave
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SELECT SUM() query question

    I have two tables with data schemed as below:
    Code:
    invoice_items
          | ii_id | invoice_id | item_id | item_ref | item_quantity | item_price |
          
          pricelist
     | item_id | ref | part | description | price | cost |
    I want to retreive the sum ( invoice_items.item_quantity * pricelist.cost ) for each invoice_item.item_id. I tried this:

    Code:
    SELECT SUM( pl.cost ) AS totalCost
         FROM pricelist pl
     LEFT JOIN invoice_items inv ON inv.item_id = pl.item_id
    but I think it gave me a cartesian product since the sum returned was huge.

    But when I did this ( which is what actually what I needed ):
    Code:
    SELECT SUM( pl.cost * inv.item_quantity ) AS totalCost
          FROM pricelist pl
          LEFT JOIN invoice_items inv ON inv.item_id = pl.item_id
    I think I came up with the right figure.

    Retreiving the correct sum is very important. So my question is: Is the second query written correctly, and if not, what should be the correct way to write it?

    Thanks
    Ducharme's Axiom: "If you view your problem closely
    enough, you will recognize yourself as part of the problem."


  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    if you want a sum for each item_id, then item_id must be in the SELECT and also the GROUP BY

    otherwise, you get just one number for the entire table
    Code:
    select pl.item_id
         , sum(pl.cost * inv.item_quantity) as totalcost
      from pricelist pl
    left outer
      join invoice_items inv 
        on pl.item_id
         = inv.item_id 
    group
        by pl.item_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    I want my 4th arrow! garlinto's Avatar
    Join Date
    Jun 2002
    Location
    Riding the electron wave
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you sir.
    Ducharme's Axiom: "If you view your problem closely
    enough, you will recognize yourself as part of the problem."



Bookmarks

Posting Permissions

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