Results 1 to 4 of 4

Thread: Sum

  1. #1
    Join Date
    Oct 2012
    Posts
    7

    Unanswered: Sum

    I have the following query:

    Code:
    SELECT table1.attention, table2.tag, table3.ord_qty
    FROM table1, table2, table3
    WHERE table2.ob_oid = mlknt.table1.ob_oid
    AND table3.sku = table2.sku 
    AND table1.attention="WR18834"
    ORDER BY table2.tag ASC;
    This pulls the correct info. However I get mulitple table2.tag lines, each with a different quantity. That is correct, but I would like to sum up all the rows and have one total for each unique value in table2.tag.

    I understand there is a SUM function, but not sure how to get it to SUM unique values in the table3.ord_qty field based on the table2.tag field.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT table1.attention
         , table2.tag
         , SUM(table3.ord_qty) AS total_ord_qty
      FROM table1
    INNER
      JOIN table2
        ON table2.ob_oid = table1.ob_oid 
    INNER
      JOIN table3
        ON table3.sku = table2.sku 
     WHERE table1.attention = 'WR18834'
    GROUP
        BY table1.attention
         , table2.tag 
    ORDER 
        BY table1.attention
         , table2.tag
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2012
    Posts
    7
    Thanks for the reply. The query you posted runs and it gives me total number for the entire attention level. With my original query I get something like:

    Code:
    attention	            tag	                qty
    WR18834              T87654  	          	96
    WR18834              T87654   	          	102
    WR18834              T87654   	          	144
    WR18834              T87655   	          	144
    WR18834              T87655   	          	78
    WR18834              T87655   	          	66
    WR18834              T87656                 	24
    WR18834              T87656                 	144
    WR18834              T87656                 	6
    The tag column has multiples of the same tag number. Within the business logic, that makes sense, so that isn't necessarily wrong. I would just like to total up all the entries for each tag and just have one line per tag:
    Code:
    attention	            tag	                qty
    WR18834              T87654  	          	342
    WR18834              T87655   	          	288
    WR18834              T87656   	          	174

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jerim79 View Post
    The query you posted runs and it gives me total number for the entire attention level.
    nope

    look at the GROUP BY clause

    it gives you the sum for each tag within each attention
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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