# a Running Total of votes?

Hello!
I need to create a query that performas a running total of votes on my mysql 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.

i.e.

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

Thanks!
Richard

###### Who is Participating?

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
0

Commented:
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
0

Commented:
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
Group by vote_date

HTH
0

Author Commented:
no. I need a RUNNING TOTAL ... so each day is added to the SUM'd total as the query progresses.
0

Commented:
Does your version of mysql support subqueries? If so, it's very straight forward, if not, stil possible but more messy ..

Sam

0

Commented:
Just as a matter of interest, how is this different from my original comment? Didn't you say you needed Running totals?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.