CODEWITHSUNDEEP
sqlpostgresql
CodyBugstein
CodyBugstein

Reputation: 23362

SQL Query to do grouping across a join

I've an enrollment table containing student IDs, course IDs and teacher IDs.

___________________
| sID | cID | tID |
___________________
| 1   | 1   |  1  |
| 1   | 2   |  2  |
| 1   | 3   |  3  |
| 2   | 1   |  1  |
| 2   | 3   |  5  |
| 3   | 1   |  1  |
| 3   | 2   |  2  |

I would like to get a table that can tell me how many students are in each course with a given professor. In other words, I'd like this:

_____________________________
| cID | tID | numOfStudents |
____________________________
| 1   | 1   |       3       |
| 2   | 2   |       2       |
| 3   | 3   |       1       |
| 3   | 5   |       1       |

I've tried 

SELECT cID, tID, count(sID) 
FROM enrollment 
GROUP BY tID

but this type of formula, with different combinations is not working for me. Does anyone have any other suggestions?

Upvotes: 0

Views: 43

Answers (2)

Filipe Silva
Filipe Silva

Reputation: 21677

Just add cid to the GROUP BY:

SELECT cID, tID, count(*) 
FROM enrollment 
GROUP BY cid,tID

sqlfiddle demo

From the docs:

When GROUP BY is present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions, since there would be more than one possible value to return for an ungrouped column.

Upvotes: 2

Val
Val

Reputation: 413

SELECT cID, tID, count(sID) FROM enrollment GROUP BY 1,2

Upvotes: 0

Related Questions