Results 1 to 14 of 14
  1. #1
    Join Date
    Dec 2012
    Posts
    23

    Unanswered: Sum(qty * price) failure

    Hi,
    Happy New Year!

    With this code, I want a total per each company of what was (shipped * price) for last month. For Example:
    101 Acme Inc. $2500
    102 Acme Co. $760

    The QTY * Price are inconsistantly incorrect. Sometimes they are correct - some times wrong.
    Code:
    SELECT C.ID, C.NAME, SUM(SH.QTY * OL.PRICE) AS [TOTAL DOLLARS] 
    FROM CUSTOMER C 
           	INNER JOIN USERS  U ON C.ID = U.ID 
            INNER JOIN ORDERS O ON C.ID = O.ID 
            INNER JOIN ORDER_LINE  OL ON O.ID = OL.ORDER_ID 
            INNER JOIN SHIP_LINE SL ON OL.ORDER_ID = SL.ORDER_ID 
           	INNER JOIN SHIPPER SH ON SL.PACKAGE_ID = SH.PACKAGE_ID 
    WHERE U.P_ID = 'CCNNTT' AND
               U.D_ID = 'U-0022' AND
             ((SH.SHIPPED_DATE >= DATEADD(MM,DATEDIFF(MM,0,GETDATE())-1,0)) AND
              (SH.SHIPPED_DATE < DATEADD(MM,DATEDIFF(MM,0,GETDATE()),0))) 
    GROUP BY C.ID, C.NAME;
    For a TEST I printed out the data. The last column has the correct math(product).
    HTML Code:
    ID      Company       qty       Price        date     output    should be:
    101	Acme Inc	8	8.99	12/11/2012	71.92	71.92
    101	Acme Inc	8	20.54	12/11/2012	164.32	164.32
    101	Acme Inc	8	25	12/12/2012	200	200
    101	Acme Inc	40	8.99	12/11/2012	359.6	359.6
    101	Acme Inc	40	20.54	12/11/2012	821.6	821.6
    101	Acme Inc	300	0.9	12/19/2012	1080	270
    101	Acme Inc	2500	0.79	12/18/2012	1975	1975
    102	Acme Co.	1	38.22	12/16/2012	76.44	38.22
    102	Acme Co.	1	40.93	12/16/2012	81.86	40.93
    102	Acme Co.	1	42.1	12/15/2012	84.2	42.1
    102	Acme Co.	1	51.64	12/15/2012	103.28	51.64
    102	Acme Co.	1	52.3	12/20/2012	52.3	52.3
    102	Acme Co.	1	53.4	12/15/2012	106.8	53.4
    I see that the product is wrong, but how to fix it??

    TIA
    Last edited by PennyLayne; 01-04-13 at 21:08.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Do you really mean sum(quantity * price) or should that read sum(quantity)*price?


    i'm guessing you have 4 rows for line 7 and 2 for the rest that are showing the wrong value
    Last edited by healdem; 01-05-13 at 04:53.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2012
    Posts
    23
    I have the SUM as this:

    SUM(SH.QTY * OL.PRICE)

    should I SUM(price) * qty ?

    row 6,8,9,10,11,13 in my example are resulting in incorrect products.

  4. #4
    Join Date
    Dec 2012
    Posts
    23
    I tried:
    SUM(OL.PRICE) * SH.QTY

    and the output is still not correct

  5. #5
    Join Date
    Dec 2012
    Posts
    23
    OK, I tried this => removing the SUM() and it is multiplying correctly.
    I must be using the SUM() incorrectly. I need a Total Sum for each company.
    Please advise. Thanks

    Code:
    SELECT C.ID, C.NAME, SH.QTY * OL.PRICE AS [TOTAL DOLLARS] 
    FROM CUSTOMER C 
           	INNER JOIN USERS  U ON C.ID = U.ID 
            INNER JOIN ORDERS O ON C.ID = O.ID 
            INNER JOIN ORDER_LINE  OL ON O.ID = OL.ORDER_ID 
            INNER JOIN SHIP_LINE SL ON OL.ORDER_ID = SL.ORDER_ID 
           	INNER JOIN SHIPPER SH ON SL.PACKAGE_ID = SH.PACKAGE_ID 
    WHERE U.P_ID = 'CCNNTT' AND
               U.D_ID = 'U-0022' AND
             ((SH.SHIPPED_DATE >= DATEADD(MM,DATEDIFF(MM,0,GETDATE())-1,0)) AND
              (SH.SHIPPED_DATE < DATEADD(MM,DATEDIFF(MM,0,GETDATE()),0))) 
    GROUP BY C.ID, C.NAME, SH.QTY, OL.PRICE;

  6. #6
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    As healdem mentions, you have duplicate rows. The Group By is preventing you from seeing them. Using your last example, change the GROUP BY to ORDER BY. This should allow you to see the duplicate rows.
    Code:
    SELECT C.ID, C.NAME, SH.QTY * OL.PRICE AS [TOTAL DOLLARS] 
    FROM CUSTOMER C 
           	INNER JOIN USERS  U ON C.ID = U.ID 
            INNER JOIN ORDERS O ON C.ID = O.ID 
            INNER JOIN ORDER_LINE  OL ON O.ID = OL.ORDER_ID 
            INNER JOIN SHIP_LINE SL ON OL.ORDER_ID = SL.ORDER_ID 
           	INNER JOIN SHIPPER SH ON SL.PACKAGE_ID = SH.PACKAGE_ID 
    WHERE U.P_ID = 'CCNNTT' AND
               U.D_ID = 'U-0022' AND
             ((SH.SHIPPED_DATE >= DATEADD(MM,DATEDIFF(MM,0,GETDATE())-1,0)) AND
              (SH.SHIPPED_DATE < DATEADD(MM,DATEDIFF(MM,0,GETDATE()),0))) 
    ORDER BY C.ID, C.NAME, SH.QTY, OL.PRICE;

  7. #7
    Join Date
    Dec 2012
    Posts
    23
    Ahh, yes I see the duplicate rows now.

    How would I modify my original query to get a dollar sum for each company?
    Without including duplicate rows?

  8. #8
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    That is more difficult to determine from 'here'.
    A) is there duplicate data one (or more) of the tables? If there is write the SQL so only one is returned.
    B) if there is no duplicate data in the tables, you must be generating the duplicate row by joining non-unique columns. Try to use Unique columns when joining.

    If you can't get rid of the duplicates any other way, you can use the last query (with the GROUP BY) as a derived table and then do the SUM. But I would try not retrieving/generating the duplicates first.

  9. #9
    Join Date
    Dec 2012
    Posts
    23
    How would I write the SQL to return only one record, not duplicates?

  10. #10
    Join Date
    Dec 2012
    Posts
    23
    when I place it this way, there are no duplicates and the numbers are correct:

    Code:
    SELECT DISTINCT C.ID, C.NAME, SH.QTY * OL.PRICE AS [TOTAL DOLLARS] 
    FROM CUSTOMER C 
           	INNER JOIN USERS  U ON C.ID = U.ID 
            INNER JOIN ORDERS O ON C.ID = O.ID 
            INNER JOIN ORDER_LINE  OL ON O.ID = OL.ORDER_ID 
            INNER JOIN SHIP_LINE SL ON OL.ORDER_ID = SL.ORDER_ID 
           	INNER JOIN SHIPPER SH ON SL.PACKAGE_ID = SH.PACKAGE_ID 
    WHERE U.P_ID = 'CCNNTT' AND
               U.D_ID = 'U-0022' AND
             ((SH.SHIPPED_DATE >= DATEADD(MM,DATEDIFF(MM,0,GETDATE())-1,0)) AND
              (SH.SHIPPED_DATE < DATEADD(MM,DATEDIFF(MM,0,GETDATE()),0))) 
    ORDER BY C.NAME;
    But as soon as I add SUM() around the multiplication and add the GROUP BY clause, the duplicates are included in the sum and the numbers are incorrect.
    So I've narrowed it down to that, but I don't know how to fix it. I keep trying though.
    I need a total sum for each company, as indicated in my original post. thanks
    Last edited by PennyLayne; 01-06-13 at 18:29.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    remove the DISTINCT to see the duplicates

    also, add the PK/FK columns to the SELECT clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    This would not be my first choice to solve the problem but it should work:
    Code:
    SELECT ID, NAME, SUM(QTY * PRICE) AS [TOTAL DOLLARS] 
    FROM (
          SELECT DISTINCT C.ID, C.NAME, SH.QTY, OL.PRICE
          FROM CUSTOMER C 
               INNER JOIN USERS  U ON C.ID = U.ID 
               INNER JOIN ORDERS O ON C.ID = O.ID 
               INNER JOIN ORDER_LINE  OL ON O.ID = OL.ORDER_ID 
               INNER JOIN SHIP_LINE SL ON OL.ORDER_ID = SL.ORDER_ID 
               INNER JOIN SHIPPER SH ON SL.PACKAGE_ID = SH.PACKAGE_ID 
          WHERE U.P_ID = 'CCNNTT' 
            AND U.D_ID = 'U-0022' 
            AND ((SH.SHIPPED_DATE >= DATEADD(MM,DATEDIFF(MM,0,GETDATE())-1,0))
            AND   (SH.SHIPPED_DATE < DATEADD(MM,DATEDIFF(MM,0,GETDATE()),0))) 
         ) AS A
    GROUP BY ID, NAME;

  13. #13
    Join Date
    Dec 2012
    Posts
    23
    @Stealth_DBA,

    Your code appears to work. I say *appears because I expected another price for 1 of the companies, but all of the other companies are summing correctly. I suspect that the price I expected for that 1 company probably had duplicates included in it.
    Thank you for your assistance.

    @r937,
    Thanks for your input. Curious what you where getting at. What are PK/FK columns?

  14. #14
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    PK/FK are Primary Keys and Foreign Keys. It is common practice that tables are related to each other by them and they are normally used as the column(s) to join tables together. (This is a very simplistic explanation.)

    As for the 'missing' company, the code supplied would NOT remove it. All the DISTINCT does is keep 1 row of any duplicate rows. Also, SUM or GROUP BY wouldn't eliminate any row completely. Just summarize down to the Unique set of rows per Columns in the GROUP BY.

Posting Permissions

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