CODEWITHSUNDEEP
mysqlsqlpostgresqlsqlite
Prpaa
Prpaa

Reputation: 245

how to write COUNT(DISTINCT()) query?

Please help me with this query.

I have a table student which looks like:

*id, name, surname, year, course*
1, John, Johnson, 3, Economy
2, Lara, Croft, 2, Biology
3, Mark, Jones, 3, Economy
4, Jim, Smith, 1, IT
5, Sarah, Kennedy, 1, IT
6, Matt, Damon, 3, Economy

And would like to get as a Result Set all courses with number of students attending, like:

*course, count*
Economy, 3
IT, 2
Biology, 1

Upvotes: 1

Views: 3187

Answers (1)

Guffa
Guffa

Reputation: 700152

You wouldn't use count(distinct ...) for this, just group on the course and use count():

select
  cource,
  count(*)
from
  Students
group by
  cource
order by
  cource

Using count(distinct ...) is useful if you want to make a distinct count within a group, for example counting the different values for year (in the implicit group of all the records):

select
  count(distinct year)
from
  Students

This would give you the result 3 as there are the three distinct values 1, 2 and 3.

Upvotes: 2

Related Questions