Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2011
    Posts
    7

    Unanswered: Trying to get MAX result from this Query

    Hello, I am trying to get the customer that has purchased the highest amount of merch. I can get the total amount for every customer, but all I need is for it to return the customer with the highest amount of purchases. I have tried everything I know...May have a mental block or just over thinking. Any help will be greatly appreciated in advance!

    SELECT c.customerid, companyname, SUM((unitprice * orderquantity) * (1 - (discount/100))) AS TOTAL_SPENT
    FROM customer c, custorder o, custorderline cu
    WHERE c.customerid = o.customerid
    AND o.orderid = cu.orderid
    GROUP BY c.customerid, companyname;

    This query returns all customers, not just the highest.
    Last edited by jmax; 10-20-11 at 19:54. Reason: Title spelling

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    below is a simple example that might get you pointed in the correct direction

    Code:
    SQL> select * from emp where emp_sal = (select max(emp_sal) from emp);
    
        EMP_ID EMP_NAME   EMP_SAL			     MEP_HIREDATE
    ---------- ---------- ------------------------------ -------------------
          COMM
    ----------
    	 1 ED	      1 			     2010-12-20 09:40:50
    	 2
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2011
    Posts
    7

    Thanks for the Direction!

    Here is all that I have tried....Of course none of them work.....Need more direction!

    SELECT c.customerid, companyname, SUM(stockprice) AS TOTAL_SPENT
    FROM customer c, custorder o, custorderline cu, inventorypart i
    WHERE c.customerid = o.customerid
    AND o.orderid = cu.orderid
    AND cu.partnumber = i.partnumber
    GROUP BY c.customerid, companyname
    HAVING SUM(stockprice) > (SELECT MAX(SUM(stockprice)) FROM inventorypart
    GROUP BY c.customerid);
    ************************************************** *******

    SELECT c.customerid, companyname, SUM((unitprice * orderquantity) * (1 - (discount/100))) AS TOTAL_SPENT
    FROM customer c, custorder o, custorderline cu
    WHERE c.customerid = o.customerid
    AND o.orderid = cu.orderid
    GROUP BY c.customerid, companyname;

    (SELECT MAX(query1.TOTAL_SPENT) AS HIGHEST_AMOUNT
    FROM customer c, custorder o, custorderline cu
    GROUP BY c.customerid, companyname) query2
    ************************************************** *******
    These are just me trying to make it work.....

    HAVING (SUM(discount)) > (SELECT SUM(stockprice) FROM inventorypart
    GROUP BY c.customerid);

    (SELECT SUM(stockprice) FROM inventorypart
    GROUP BY c.customerid) > 6000;


    SELECT SUM(unitprice) FROM custorderline;

    GROUP BY customerid;

    SUM((unitprice * orderquantity) * (1 - (discount/100))) AS TOTAL_SPENT

    SELECT * FROM custorderline;
    ************************************************** *******
    Trying to do something from online.......

    select query1.* from
    (SELECT c.customerid, companyname, SUM((unitprice * orderquantity) * (1 - (discount/100))) AS TOTAL_SPENT
    FROM customer c, custorder o, custorderline cu
    GROUP BY c.customerid) query1,

    (select max(query2.total_amt) as highest_amt
    from (SELECT c.customerid, companyname, SUM((unitprice * orderquantity) * (1 - (discount/100))) AS TOTAL_SPENT
    FROM customer c, custorder o, custorderline cu
    GROUP BY c.customerid) query2) query3)
    where query1.total_amt = query3.highest_amt;

    I AM STUMPED?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    alternatively
    Code:
    SQL> select max(empdob) from employees;
    
    MAX(EMPDOB)
    -------------------
    1985-01-15 00:00:00
    
    SQL> select empid, empdob from
      2  ( select * from employees order by empdob DESC)
      3  where rownum = 1;
    
         EMPID EMPDOB
    ---------- -------------------
         15560 1985-01-15 00:00:00
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Oct 2011
    Posts
    7

    I see the light!

    NOW, I SEE THE LIGHT!

    Thank You!

Posting Permissions

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