-1

I am working on a mock database for a school project and I have two tables: CUSTOMERS AND ORDERS Both tables look like this

CUSTOMERS: custID, custLName, custFName, custAddress, custTown, custPostcode, custPhone, custEmail

And

ORDERS: orderID, orderDate, dispatchDate, custID (foreign key)

I am trying to generate a query that returns the full name and phone number of the customer who has made the most orders. This is my query below however it is returning an error on the GROUP BY function stating ORA-00904: "CUSTOMER": invalid identifier

SELECT b.custFName || ' ' || b.custLName || ', ' || b.custPhone AS Customer, 
COUNT(DISTINCT o.custID) AS Orders_Placed
FROM CUSTOMERS b, ORDERS o 
GROUP BY Customer
HAVING COUNT(DISTINCT o.custID) AND b.custID = o.custID
ORDER BY o.custID DESC;
Cœur
  • 37,241
  • 25
  • 195
  • 267
Dylan52
  • 61
  • 2
  • 10
  • 1
    Oracle doesn't recognize aliases for `group by`. You need to repeat the expression. You should also learn to use proper, explicit, **standard** `JOIN` syntax. – Gordon Linoff Oct 11 '18 at 13:55
  • 1
    To add to what Gordon said about learning the ANSI join syntax - you currently have no join conditions specified, so you're doing a cross join (i.e. each row in the customers table will match to every row in orders), so you will most likely end up with loads of rows you weren't expecting. Using the explicit ANSI join syntax (e.g. `select ... from t1 inner join t2 on t1.col1 = t2.col1 ...`) helps you to remember that you need to specify the join conditions! – Boneist Oct 11 '18 at 14:14
  • The `GROUP BY` should be by `b.custid`, in addition to all the other points. Your code is full of mistakes; you don't just need help with one thing, you need a lot of help. Your instructor's office hours may be a much better place to get help for THAT kind of situation. –  Oct 11 '18 at 15:11

1 Answers1

0

Adding to what Gordon Linoff and Boneist said, you are also not filtering in your HAVING clause, on top of all the other mistakes mentioned. Work on doing your joins properly then filtering/ selecting the record with the MAX count.

Aaron Aexander
  • 46
  • 4