count the 3 different values ​that a column can take

I’m trying to count the 3 different values ​​that a column in a table can take, in a single SELECT. What am I doing wrong?

with cls.getDictCursor() as cur:
    cur.execute("SELECT s.matricola,r.lingua, r.livello,COUNT (CASE WHEN tipoProva=0 THAN 1) AS testInformatizzato,
        COUNT (CASE WHEN tipoProva=1 THAN 1) AS provaScritta,
        COUNT (CASE WHEN tipoProva=2 THAN 1) AS provaOrale 
        FROM risultatoprovacla AS r 
            JOIN studentecla AS s ON s.id = r.id_risultatocladistu_studentecla 
        WHERE s.matricola ILIKE %s 
        GROUP BY s.matricola,r.lingua,r.livello
        ORDER BY r.lingua;", (matricola,))

I am using a postgresql table where the column named “tipoprova” is an integer

Answer

You are looking for filtered aggregation:

SELECT s.matricola,r.lingua, r.livello,
       COUNT(*) filter (where tipoProva=0) AS testInformatizzato,
       COUNT(*) filter (where tipoProva=1) AS provaScritta,
       COUNT(*) filter (where tipoProva=2) AS provaOrale 
FROM risultatoprovacla AS r 
  JOIN studentecla AS s ON s.id = r.id_risultatocladistu_studentecla 
WHERE s.matricola ILIKE %s 
GROUP BY s.matricola,r.lingua,r.livello
ORDER BY r.lingua;

---

Your query is wrong, because you have the wrong syntax for the CASE expression. It needs to be THEN not THAN and it also needs to be ended with END, e.g.:

COUNT(CASE WHEN tipoProva=1 THEN 1 END) AS provaScritta

would be the correct syntax