Skip to content

Instantly share code, notes, and snippets.

@mattandrews

mattandrews/averages.sql

Created July 8, 2019 11:36
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save mattandrews/7c4b45993b0070203477357839926fdc to your computer and use it in GitHub Desktop.
Save mattandrews/7c4b45993b0070203477357839926fdc to your computer and use it in GitHub Desktop.
Download ZIP
Get average votes for yes, month-by-month
Raw
averages.sql
select DATE_FORMAT(createdAt, "%Y-%m-01") AS month, count(choice) as votesTotal,
(SELECT count(choice)
FROM survey
WHERE choice = 'no'
AND YEAR(createdAt) = YEAR(month) AND MONTH(createdAt) = MONTH(month)
) AS numNoVotes,
(SELECT 100 - numNoVotes / count(choice) * 100) AS percentageVotedYes
from survey
GROUP BY DATE_FORMAT(createdAt, "%Y-%m-01")
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment