#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    30
    Rep Power
    2

    Question Display customers living in GA or FL who recently placed order totaling > $80.


    Okay, what I want my code to do is display the customers who live in either Georgia or Florida who recently placed an order totaling more than $80.

    My code so far:

    Code:
    SELECT LastName, FirstName, SUM(Retail * Quantity) AS Total
    From CUSTOMERS, ORDERS, ORDERITEMS, BOOKS
    Where  (State = 'FL' OR State = 'GA') 
    Group by customers.customer# Having SUM(Retail * Quantity) > 80.00;
    How would I get this to work?
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    858
    Rep Power
    388
    Originally Posted by darthvader45
    Okay, what I want . . .
    However, I keep getting a "not a GROUP BY expression" error. I have tried several different iterations of it, all giving the same error.

    Anybody know where I'm going wrong here?
    The error tell it all, you need to group by whatever columns you select that are not "aggregated":
    Code:
      SELECT lastname, firstname, SUM ( retail * quantity) AS total
        FROM customers
           , orders
           , orderitems
           , books
       WHERE state IN ( 'FL','GA')
    GROUP BY lastname, firstname
      HAVING SUM ( retail * quantity) > 80.00;
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    103
    Also one assumes that there is data that meets your needs within that table as well too!
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    30
    Rep Power
    2
    Originally Posted by LKBrwn_DBA
    The error tell it all, you need to group by whatever columns you select that are not "aggregated":
    Code:
      SELECT lastname, firstname, SUM ( retail * quantity) AS total
        FROM customers
           , orders
           , orderitems
           , books
       WHERE state IN ( 'FL','GA')
    GROUP BY lastname, firstname
      HAVING SUM ( retail * quantity) > 80.00;
    Okay, my output is as follows(I modified your code to display only the last and first names):


    All I want displayed is the customers whose orders total more than $80, i.e. morales, lucas and smith.
  8. #5
  9. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    858
    Rep Power
    388

    Cool


    Originally Posted by darthvader45
    Okay, my output is as follows(I modified your code to display only the last and first names):


    All I want displayed is the customers whose orders total more than $80, i.e. morales, lucas and smith.
    In that case, you need to provide a test case with "CREATE TABLE" , insert commands to load the data and the expected results.


IMN logo majestic logo threadwatch logo seochat tools logo