Solved

a Running Total of votes?

Posted on 2004-08-22
6
574 Views
Last Modified: 2010-05-18
Hello!
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.

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

0
Comment
Question by:rcbuchanan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 48

Expert Comment

by:hernst42
ID: 11868069
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
 
LVL 2

Expert Comment

by:nexusSam
ID: 11868688
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

HTH
0
 

Author Comment

by:rcbuchanan
ID: 11870309
no. I need a RUNNING TOTAL ... so each day is added to the SUM'd total as the query progresses.
0
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 
LVL 2

Expert Comment

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

Sam

0
 
LVL 17

Accepted Solution

by:
akshah123 earned 500 total points
ID: 11870557
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
 
LVL 2

Expert Comment

by:nexusSam
ID: 11870996
Just as a matter of interest, how is this different from my original comment? Didn't you say you needed Running totals?
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question