CODEWITHSUNDEEP
sqlcountdistinct
rekha
rekha

Reputation: 61

SQL for finding the counts per user

Lets say I have the following table:

Student      Course           University
1             a                   x
1             b                   x
1             c                   x
1             a                   y
2             a                   x
2             a                   y
2             a                   z
3             a                   x

For each student, I am trying to find the number of unique courses and universities that they are enrolled in.

The output would be as follows:

Student     No. of Courses        No. of Universities
 1             3                         2
 2             1                         3
 3             1                         1

How would I construct the SQL for this?

Upvotes: 5

Views: 10674

Answers (1)

Martin Smith
Martin Smith

Reputation: 453910

SELECT Student,
       COUNT(DISTINCT Course)     AS NumberOfCourses,
       COUNT(DISTINCT University) AS NumberOfUniversities
FROM   YourTable
GROUP  BY Student

Upvotes: 14

Related Questions