MariaDB 10.3

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table users (course int);
insert into users (course)
values (1), (1), (2), (3), (3), (3), (4), (5);
create table courses (id int, name varchar(20));
insert into courses (id, name)
values (1, 'one'), (2, 'two'), (3, 'three'), (4, 'four'), (5, 'five');
select * from users;


course


1

1

2

3

3

3

4

5


select * from courses;


id
name


1
one

2
two

3
three

4
four

5
five


SELECT u.course, COUNT(*) as freq
FROM users u
INNER JOIN courses c
ON u.course = c.id
GROUP BY u.course;


course
freq


1
2

2
1

3
3

4
1

5
1


SELECT u.course, c.name, COUNT(*) as freq
FROM users u
INNER JOIN courses c
ON u.course = c.id
GROUP BY u.course, c.name;


course
name
freq


1
one
2

2
two
1

3
three
3

4
four
1

5
five
1