1

I have 4 tables:

  • Customer (CustomerID(PK), FirstName, LastName)
  • Order (OrderID(PK), CustomerID(FK))
  • Purchase (OrderID(FK), GameID(FK), Quantity)
  • Games (GameID(PK), RetailPrice)

I would like to list the names of all customers whose orders total more than $150.

What I had that works:

SELECT 
    "p"."OrderID", SUM("p"."Quantity" * "g"."RetailPrice") "TotalPrice"
FROM 
    "Purchase" "p" 
JOIN 
    "Games" "g" ON ("p"."GameID" = "g"."GameID")
GROUP BY 
    "p"."OrderID"
HAVING 
    SUM("p"."Quantity" * "g"."RetailPrice") >= 150

I tried joining the customer and order tables, which works, but once I try to select the firstname and lastname columns, it does not work; I get an error

Not in aggregate function or group by clause

SELECT 
    "c"."FirstName", "c"."LastName", 
    "p"."OrderID", SUM("p"."Quantity" * "g"."RetailPrice") "TotalPrice"
FROM 
    "Customer" "c" 
JOIN 
    "Order" "o" ON "c"."CustomerID" = "o"."CustomerID" 
JOIN 
    "Purchase" "p" ON "o"."OrderID" = "p"."OrderID" 
JOIN  
    "Games" "g" ON ("p"."GameID" = "g"."GameID")
GROUP BY 
    "p"."OrderID"
HAVING 
    SUM("p"."Quantity" * "g"."RetailPrice") >= 150

None of the explanations I've seen online relating to aggregate functions and joining tables have been sufficient to make me understand what I need to do to make this work. What is the correct query for this and why is that the correct query?

user236529
  • 115
  • 1
  • 8
  • Which RDBMS is this for? Please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. – marc_s Mar 25 '20 at 04:47
  • Libreoffice Base. – user236529 Mar 25 '20 at 04:53
  • `SUM` is an aggregate function. Therefore all items in the `SELECT` must either be themselves aggregates or appear in the `GROUP BY`. What you need to do, is to use your working query as a sub-query and join the other tables to the sub-query. – Jonathan Willcock Mar 25 '20 at 05:05
  • Oh it seems adding firstname and lastname to Group By solves it. I could've sworn I tried that... – user236529 Mar 25 '20 at 06:50
  • 1
    LO Base is not the name of the engine. It can be embedded with either `hsqldb` (older) or `firebird` (newer). Or, it can use a split db setup with other engines such as `mysql`. In the future, you should determine which engine you are using and then add the appropriate tag. – Jim K Mar 25 '20 at 15:43

0 Answers0