CODEWITHSUNDEEP
sqlpostgresql
Mateusz
Mateusz

Reputation: 624

Counting occuriences from another table in SQL

I have the table teachers (each teacher has got only one module, but module can have many teachers):

SELECT * from teachers;

ID | teachers| module_id | year
-------------------------------
Int| varchar | Int       | Int
-------------------------------
1  | Jack    | 12        | 2015
2  | John    | 56        | 2016
3  | Alex    | 12        | 2015
4  | Tony    | 56        | 2015
5  | Matt    | 56        | 2015
6  | Pete    | 12        | 2016
7  | Boby    | 14        | 2014

And so on...

And the table modules:

SELECT * from modules
module_id_| title    
--------------------
Int       | varchar    
--------------------
12        | Maths    
56        | PE       
14        | Biology

And I would like to have a query that give me a list of modules and how many teachers have them and in which year(and sort by this year). So the result would be

PE, 3, 2015, 2016 (in 2015 whas most)
Maths , 3, 2015, 2016
Biology , 1, 2014

EDIT:

Result of the answer below is this, and its not what I wanted:

 count | year | title
-----------------------
   1   | 2015 | Maths 
   1   | 2016 | PE 
   1   | 2015 | Maths 
   1   | 2015 | PE 
   1   | 2015 | PE 
   1   | 2016 | Maths 
   1   | 2014 | Biology

Upvotes: 0

Views: 48

Answers (1)

Ildar Musin
Ildar Musin

Reputation: 1468

I think it could be something like this:

SELECT title, count(*), array_agg(distinct year order by year)
FROM modules m
JOIN teachers t ON t.module_id = m.module_id_
GROUP BY title;

Upvotes: 1

Related Questions