sql query to count distinct

Question

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

thanks for help

user_3986005 · Answer

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...)