need assistance with Select distinct entries

Question

I'm trying to Select and list only the Courses where more than 2 students are registered, I'm executing the following query but its giving me errors, please help

SELECT DISTINCT COURSES FROM STUDENTTBL WHEN COUNT(COURSES)>2;  

Courses      Students
Maths        Dave
Maths        John
Maths        Nancy
Maths        Ron
History      Dave
History      John
History      Nancy
Science      Dave
Science      john

the expected output is

Maths
History

Try including the exact error you are receiving – ste-fu Mar 20 '16 at 04:56 — ste-fu, Mar 20 '16 at 04:56

score 1 · Accepted Answer · edited May 23 '17 at 11:45

You need to use GROUP BY on COURSES and then need to check the corresponding count of courses greater than 2 using HAVING

SELECT COURSES FROM 
STUDENTTBL 
GROUP BY COURSES
HAVING COUNT(COURSES)>2;

When you group by on something then you will get one entry for that something.

More:

Distinct is used to filter unique records out of the records that satisfy the query criteria.

Group by clause is used to group the data upon which the aggregate functions are fired and the output is returned based on the columns in the group by clause. It has its own limitations such as all the columns that are in the select query apart from the aggregate functions have to be the part of the Group by clause.

Learn more on distinct and group by

need assistance with Select distinct entries

1 Answers1