[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 585
  • Last Modified:

a Running Total of votes?

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
rcbuchanan
Asked:
rcbuchanan
1 Solution
 
hernst42Commented:
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
 
nexusSamCommented:
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
 
rcbuchananAuthor Commented:
no. I need a RUNNING TOTAL ... so each day is added to the SUM'd total as the query progresses.
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

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

Sam

0
 
akshah123Commented:
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
 
nexusSamCommented:
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

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now