Calculating and displating voting results

[taffd]

Results
The results pages of myverdict.net have been the most difficult so far. It took me a month of research to find out how to write the sql query. I eventually found the answer in an added comment in mysql documentation. Here was my problem. I had a table with a vote column, the entries for which could be For, Against or Undecided. Counting the total votes was easy enough and by grouping I could return a count for each. However, I wanted to display results for every question in a particular category, on one page, using a repeating table, the results reading across the page. A repeating region only displays one row at a time and my simple grouped query would not do as it returned multiple rows.
Here then, for the sql buffs out there is the query that worked.

SELECT questions.question, COUNT(votes.vote) AS total,
COUNT(votes.vote = ‘For’ OR NULL) AS col1,
COUNT(votes.vote = ‘Against’ OR NULL) AS col2,
COUNT(votes.vote = ‘Undecided’ OR NULL) AS col3
FROM questions, votes
WHERE questions.questionID = votes.questionID
GROUP BY questions.question

You apparently need the ‘OR NULL’ or else it doesn’t work, I don’t know why. Anyway it was a simple matter to display col1, col2, col3 and total votes in the repeating region of my page. (see results pages on www.myverdict.net)

[guelphdad]

actually you should use CASE as it is standard sql, so for example instead of


COUNT(votes.vote = ‘For’ OR NULL) AS col1
you would use:

COUNT(case when votes.vote='For' THEN 1 ELSE NULL END) as col1

you should always strive for portability in your code because you never know when you are going to have to switch databases.