SQL Help

Discussion in 'OT Technology' started by r1dd1ck, Mar 28, 2008.

  1. r1dd1ck

    r1dd1ck New Member

    Joined:
    Nov 8, 2007
    Messages:
    32
    Likes Received:
    0
    I need some help with this SQL query I am writing.
    I know it involves some kind of sub-query but I am stumped. Any help would be much appreciated.

    This is what I have so far:
    Code:
    SELECT c.cus_code, c.cus_balance, l.line_units*l.line_price AS TOTAL_PURCHASES 
    FROM customer c, line l, invoice i
    WHERE c.cus_code = i.cus_code
    AND i.inv_number = l.inv_number
    ORDER BY cus_code
    And the output looks like this:

    Code:
    CUS_CODE   CUS_BALANCE   TOTAL_PURCHASES
    10011            0        29.35
    10011            0         9.95          
    10011            0         19.9          
    10011            0        14.97          
    10011            0         9.98          
    10011            0       359.85          
    10012       345.86        74.95          
    10012       345.86        38.95          
    10012       345.86        39.95          
    10014            0       256.99          
    10014            0         9.95          
    10014            0       109.92          
    10014            0        20.97          
    10014            0        14.99          
    10014            0         9.95          
    10015            0        29.98          
    10015            0         4.99          
    10018       216.55        70.44
    I need it to add together the TOTAL_PURCHASES column for each CUS_CODE

    It should look like this:

    Code:
    CUS_CODE   CUS_BALANCE   TOTAL_PURCHASES
    10011            0        444.00        
    10012       345.86        153.85                 
    10014            0        422.77                 
    10015            0         34.97               
    10018       216.55         70.44
     
  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!
    Code:
    SELECT c.cus_code, c.cus_balance, SUM(l.line_units*l.line_price) AS TOTAL_PURCHASES 
    FROM customer c, line l, invoice i
    WHERE c.cus_code = i.cus_code
       AND i.inv_number = l.inv_number
    GROUP BY c.cus_code, c.cus_balance
    ORDER BY cus_code
    
     
  3. r1dd1ck

    r1dd1ck New Member

    Joined:
    Nov 8, 2007
    Messages:
    32
    Likes Received:
    0
    :bowdown:

    Thank you so much Sled!
     
  4. r1dd1ck

    r1dd1ck New Member

    Joined:
    Nov 8, 2007
    Messages:
    32
    Likes Received:
    0
    is there a way to take what you gave me and list the amount of purchases each cus_code made?

    For example 10011 had 6 purchases.
     
  5. You can select the COUNT of invoices or whatever you wanted for each customer.

    EDIT: Like this bit here gives me the number of transactions for the specified delivery address id.


    SELECT COUNT(AccountsTransactionID) AS Expr1
    FROM AccountsTransaction
    WHERE (DeliveryAddressID = 64324)
     
    Last edited by a moderator: Mar 28, 2008
  6. r1dd1ck

    r1dd1ck New Member

    Joined:
    Nov 8, 2007
    Messages:
    32
    Likes Received:
    0
    thanks for the help Market Garden! :bigthumb:
     
  7. r1dd1ck

    r1dd1ck New Member

    Joined:
    Nov 8, 2007
    Messages:
    32
    Likes Received:
    0
    Did I mention that SQL makes me want to beat my head against the wall?
    This is the last problem i swear.

    I am trying with the min and max statements to get the outputs for each customer number, my output is the entire database min and max

    Code:
    SELECT c.cus_code,
    COUNT(l.inv_number) as NUMBER_OF_INVOICES,
    to_char((SUM(l.line_units*l.line_price)/COUNT(l.line_units)), '$9999.99') AS AVG_PRODUCTS_PURCHASED,
    (SELECT MIN(l.line_price) FROM line l) AS MIN_INV_AMT,
    (SELECT MAX(l.line_price) FROM line l) AS MAX_INV_AMT,
    to_char((SUM(l.line_units*l.line_price)), '$9990.99') AS TOTAL_PURCHASES 
    FROM customer c, line l, invoice i
    WHERE c.cus_code = i.cus_code
    AND i.inv_number = l.inv_number
    GROUP BY c.cus_code
    ORDER BY total_purchases DESC
    
    The incorrect output

    Code:
     CUS_CODE   NUMBER_OF_INVOICES   AVG_PRODUCTS_PURCHASED   MIN_INV_AMT   MAX_INV_AMT   TOTAL_PURCHASES          
    10011            6      $74.00         4.99       256.99     $444.00          
    10014            6      $70.46         4.99       256.99     $422.77          
    10012            3      $51.28         4.99       256.99     $153.85          
    10018            1      $70.44         4.99       256.99      $70.44          
    10015            2      $17.49         4.99       256.99      $34.97
     

Share This Page