sql query to count distinct

hi guys, T need help with this query to count the results,
the output that I what will be:

john, for the referdum, has 4 votes
mark, for the referdum has 2 votes
sofia, for the premier has 3 votes
anna, for the premier has 1 votes

SELECT DISTINCT 
    candidate, 
    position, 
    count(DISTINCT candidate) over (order by position) AS votes_received 
from votes; 

this was my query, but says:

This version of MariaDB does not yet support ‘COUNT (DISTINCT) aggregate as window function

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

thanks for help

>Solution :

I think you need to look at GROUP BY, something like:

SELECT COUNT(*) AS `total`, `candidate`, `position`
FROM `votes`
GROUP BY `candidate`, `position`

(back ticks, just in case…)