a Running Total of votes?

I need to create a query that performas a running total of votes on my mysql table : opinionvotes.

TABLE opinionvotes
userid (i.e. 143)
vote_date (i.e. 04-jun-2004)
vote_want_yes (1 or 0)
vote_want_no (1 or 0)

I need to create a query that creates a RUNNING TOTAL of the vote_want_yes and vote_want_no sorted by the vote_date ascending.


01-jan-2004 SUMvote_want_yes = 1 SUM vote_want_no = 2
02-jan-2004 SUMvote_want_yes = 5 SUM vote_want_no = 5
03-jan-2004 SUMvote_want_yes = 9 SUM vote_want_no = 8
04-jan-2004 SUMvote_want_yes = 12 SUM vote_want_no = 16
05-jan-2004 SUMvote_want_yes = 20 SUM vote_want_no = 22

Any ideas?

I tried THIS query but it returns massively high totals and I don't know why!

SELECT t1.vote_date, SUM(t2.vote_want_yes) as sumYes, SUM(t2.vote_want_no) as sumNo FROM opinionvotes t1 LEFT JOIN opinionvotes t2 on t1.vote_date >= t2.vote_date WHERE t1.opinion_id = 1057 GROUP BY t1.vote_date


Who is Participating?
akshah123Connect With a Mentor Commented:
it's simple try this

SELECT t1.vote_date, SUM(t2.vote_want_yes) as sumYes, SUM(t1.vote_want_no) as sumNo FROM opinionvotes t1
WHERE t1.opinion_id = 1057
GROUP BY t1.vote_date
Why didn't you combine the vote_want_yes and vote_want_no into one field vote_want (1 for yes, 0 for no) Then you can easy calculate the result by:

select count(*) as num, vote_want FROM opinionvotes GROUP BY vote_date, vote_want
Richard, I don't think you need a join in there at all.
Try :

SELECT vote_date, SUM(vote_want_yes) as sumYes, SUM(vote_want_no) as sumNo
FROM opinionvotes
Group by vote_date

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

rcbuchananAuthor Commented:
no. I need a RUNNING TOTAL ... so each day is added to the SUM'd total as the query progresses.
Does your version of mysql support subqueries? If so, it's very straight forward, if not, stil possible but more messy ..


Just as a matter of interest, how is this different from my original comment? Didn't you say you needed Running totals?
All Courses

From novice to tech pro — start learning today.