How many times is a foreign key used in another table?

Question

SQL

Suppose there are 2 tables (just give an example)
1 table - prof
2 table - per

CREATE TABLE  prof (
    id_prof    int(4)  NOT NULL PRIMARY KEY AUTO_INCREMENT, 
    name  varchar(40)
   )/

CREATE TABLE  per (
    id_per    int(2) NOT NULL PRIMARY KEY AUTO_INCREMENT, 
    name    varchar(40), 
    id_prof int(4) FOREIGN KEY
   ) 
/

Those. there is a table with professions (prof) and employees (per), in the table with employees there is a foreign key by which we set the profession
How to make such a query so that you can display all the data from the table with professions (prof) + a new column, where in front of each The occupation record will be the number of employees with that occupation (i.e. how many times the foreign key was used in the per table).
For example, if there are 10 employees in the table, 4 of them are programmers and 6 are administrators, then after the query the table
1 | programmer | 4
2 | administrator | 6
Thank you in advance for your reply!!

Answer 1

JOIN + GROUP BY

SELECT prof.id_prof, prof.name, COUNT(id_per)
  FROM per
  JOIN prof ON (per.id_prof = prof.id_prof)
 GROUP BY 1, 2;