I have a query in which I count the rows grouped by a type code varchar should count 1 but count the 3, you could see that it is wrong
the grouped column is ID_CURSO_UNICO
SELECT count(ID_CURSO_UNICO) as cantidad from encuesta_apl where
YEAR(fecha)=? and ID_ENCUESTA=? group by ID_CURSO_UNICO
the result it gives
Of course it counts 3, because COUNT() acts on the disaggregated data and effectively there are 3 records. You can do the following:
The most explanatory way would be to count the groups:
SELECT COUNT(1)
FROM (SELECT ID_CURSO_UNICO
from encuesta_apl
where YEAR(fecha)=? and ID_ENCUESTA=?
group by ID_CURSO_UNICO)
But the simplest:
SELECT count(DISTINCT ID_CURSO_UNICO) as cantidad
from encuesta_apl where
YEAR(fecha)=? and ID_ENCUESTA=?
The most general solution when your table has many records I recommend that you consider how many occurrences each value of the data ID_CURSO_UNICO will have and the proper SQL would be
SELECT ID_CURSO_UNICO as curso, count(1) as cantidad
from encuesta_apl
where YEAR(fecha)=? and ID_ENCUESTA=?
group by ID_CURSO_UNICO
and if you want to know only those that have more than one occurrence in the table, complement with Having staying in:
SELECT ID_CURSO_UNICO as curso, count(1) as cantidad
from encuesta_apl
where YEAR(fecha)=? and ID_ENCUESTA=?
group by ID_CURSO_UNICO
having count(1) >= 2