MySQL Forums
Forum List  »  Newbie

ERROR 1054 (42S22): Unknown column 'COUNT' in 'field list'
Posted by: carolin sha
Date: October 27, 2018 11:42AM

hi,

I want to display the name and email of the students who are registered for ALL modules.

this is my SQL expression and error I Am getting. any assistance would be appreciated.


mysql> SELECT S.SName,S.EMail
-> FROM registration R,student S
-> WHERE R.StudentId=S.StudentId
-> GROUP BY S.StudentId,S.SName,S.EMail
-> HAVING COUNT(*)=(SELECT COUNT
-> FROM module);

Error: ERROR 1054 (42S22): Unknown column 'COUNT' in 'field list'

the tables are below:

module;
+----------+------------+-------+--------+---------+----------+
| ModuleId | MName | MType | MLevel | Credits | Syllabus |
+----------+------------+-------+--------+---------+----------+
| M1 | Database | 10 | 1 | 1 | Old |
| M2 | Web | 15 | 2 | 3 | New |
| M3 | DIS | 12 | 1 | 6 | New |
| M4 | ECommerce | 20 | 3 | 9 | Old |
| M5 | Management | 20 | 2 | 3 | New |
| M6 | Business | 10 | 1 | 6 | Old |
+----------+------------+-------+--------+---------+----------+

registration;
+-----------+----------+--------+
| StudentId | ModuleId | Result |
+-----------+----------+--------+
| S1 | M5 | NULL |
| S2 | M5 | NULL |
| S3 | M1 | 76 |
| S3 | M2 | NULL |
| S3 | M5 | NULL |
| S5 | M1 | 45 |
| S5 | M2 | 32 |
| S5 | M3 | 25 |
| S5 | M4 | 90 |
| S5 | M5 | 85 |
| S5 | M6 | 45 |
+-----------+----------+--------+

student;
+-----------+---------+---------+-----+---------------------+
| StudentId | SName | Address | Age | EMail |
+-----------+---------+---------+-----+---------------------+
| S1 | John | Curepe | 20 | John@sbcs.com |
| S10 | Brown | Arima | 24 | Brown@hotmail.com |
| S2 | Patrick | Couva | 24 | Patrick@sbcs.com |
| S3 | Mary | Curepe | 29 | Mary@sbcs.com |
| S4 | Hank | Curepe | 44 | Hank@hotmail.com |
| S5 | Harry | Maracas | 34 | Harry@sbcs.com |
| S6 | Smith | Arima | 24 | Smith@gmail.com |
| S7 | White | Curepe | 24 | White@sbcs.com |
| S8 | Charles | Penal | 25 | Charles@hotmail.com |
| S9 | Khan | Arima | 25 | Khan@gmail.com |
+-----------+---------+---------+-----+---------------------+

Options: ReplyQuote


Subject
Written By
Posted
ERROR 1054 (42S22): Unknown column 'COUNT' in 'field list'
October 27, 2018 11:42AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.