Counting occuriences from another table in SQL

Question

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

I would like to do it in postgresql

Mateusz
– Mateusz

2016-02-25 13:28:21 +00:00
Commented Feb 25, 2016 at 13:28 — Mateusz
– Mateusz, Commented Feb 25, 2016 at 13:28

Ildar Musin · Accepted Answer · 2016-02-25 18:15:43Z

1

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;

edited Feb 25, 2016 at 18:15

answered Feb 25, 2016 at 13:42

Ildar Musin

1,4881 gold badge12 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mateusz Over a year ago

its wrong, it doesnt print years in one line, and it lists teachers instead of moules

Ildar Musin Over a year ago

Your output doesn't seem like output of the query above. Here is what I got: pastebin.com/raw/sWB3z542

Collectives™ on Stack Overflow

Counting occuriences from another table in SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related