sql query to count distinct

candidate position
john referendum
mark referendum
sofia premier
john referendum
john referendum
sofia premier
mark referendum
sofia premier
anna premier
john referendum

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

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

Leave a Reply