SQL count how many duplicated values of a derived table

Question

I know this gets asked a lot but no other solution worked for me.

I have this query:

SELECT 
    us.id AS idUser, ut.id AS idUtente, us.nome AS nomefunc 
FROM 
    utentes ut 
JOIN 
    historico h ON h.idUtente = ut.id 
JOIN 
    users us ON h.idMedico = us.id 
GROUP BY 
    idUser, idUtente

which returns this results:

idUser | idUtente | nomefunc
-------+----------+----------
  1    |    1     | Pedro
  1    |    2     | Pedro
  1    |    3     | Pedro
  1    |    4     | Pedro
  1    |    5     | Pedro
  2    |    1     | Filipe
  2    |    2     | Filipe
  3    |    1     | Francisco
  5    |    2     | Miguel

Now I wish to count how many times each nomefunc appears on the table. A simple count(nomefunc) won't work.

If necessarily I'll post every columns name for each table.

Thanks in advance!

Answer 1

Try the following:

select 
    nomefunc, 
    count(*)
From
(
SELECT 
    us.id AS idUser, ut.id AS idUtente, us.nome AS nomefunc 
FROM 
    utentes ut 
JOIN 
    historico h ON h.idUtente = ut.id 
JOIN 
    users us ON h.idMedico = us.id 
GROUP BY 
    idUser, idUtente
) as X
Group by
    nomefunc