CODEWITHSUNDEEP
mysqlsqldatabaseoracle-database
prodigy09
prodigy09

Reputation: 23

need assistance with Select distinct entries

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

Upvotes: 1

Views: 35

Answers (1)

1000111
1000111

Reputation: 13519

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

Upvotes: 1

Related Questions