sql query error

Question

With the following query:

SELECT SeatPref FROM (SELECT SeatPref, COUNT(CustID) AS seat_count FROM Booking 
    GROUP BY SeatPref) WHERE seat_count = max(seat_count)

I am getting the following error:

Every derived table must have its own alias.

Have you looked at this? http://stackoverflow.com/questions/1888779/every-derived-table-must-have-its-own-alias — RQDQ, Feb 17 '11 at 14:08

score 6 · Accepted Answer · answered Feb 17 '11 at 14:09

6

You are missing table alias -

SELECT t1.SeatPref 
FROM (SELECT SeatPref, COUNT(CustID) AS seat_count 
      FROM `Booking` 
      GROUP BY SeatPref)  t1
WHERE t1.seat_count = max(t1.seat_count)

answered Feb 17 '11 at 14:09

Sachin Shanbhag

52,879
11
86
103

thanx, but now it says INVALID USE OF GROUP FUNCTION – Nitin Garg Feb 17 '11 at 14:16
@Nitin - Yes. Because you have used `Count(custID)`. You should use `count(*)`. Or Include custID in your groupby. – Sachin Shanbhag Feb 17 '11 at 14:17
I want to count the no. of customerIDs for each SeatPref. Using a * would not do this, right> – Nitin Garg Feb 17 '11 at 14:21
@Nitin - Then your question is not correct. You might have to add a new question giving what results you want to get along with the table structure. – Sachin Shanbhag Feb 17 '11 at 14:23

score 1 · Answer 2 · edited Feb 17 '11 at 14:15

1

You Should add an alias to the subquery (SELECT SeatPref, COUNT(CustID) AS seat_count FROM Booking GROUP BY SeatPref).

:)

edited Feb 17 '11 at 14:15

Neil Knight

45,890
23
126
186

answered Feb 17 '11 at 14:11

JAiro

5,692
2
20
21

sql query error

2 Answers2