?
Solved

a Running Total of votes?

Posted on 2004-08-22
6
Medium Priority
?
579 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
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 2000 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

762 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