Geek Answers Handbook

Mysql query: how to get the number of yes / no votes per day

I need to create a mysql query to get the distribution of votes every day exceeding a specific date, something like this ...

    date          yes_votes    no_votes
------------------------------------------
    2010-01-07    21           22
    2010-01-07    2            0

My table is like this.

    post_votes
--------------------------
    id(longint)
    date(timestamp)
    flag(tinyint) // this stores the yes/no votes 1-yes, 2-no

I'm stuck on this ....

SELECT COUNT(*) AS count, DATE(date) FROM post_votes WHERE date > '2010-07-01' GROUP BY DATE(date)

this gives the total number of votes per day, but not the distribution that I want.

+5
source share
5 answers
SELECT COUNT(*) AS count
     , DATE(date)
     , SUM(flag = 1) AS yes_votes
     , SUM(flag = 2) AS no_votes
FROM post_votes 
WHERE date > '2010-07-01' 
GROUP BY DATE(date)

This is a trick that works in MySQL since there flag=1will be either Trueor False. But True = 1also False = 0in MySQL, so you can add 1 and 0 using a function SUM().

Other solutions with IFor CASEwill be better for clarity or if you want to move the database to another DBMS.

Comments not related to the issue:

  • date count .
  • "", . .
  • (post_vote), , , . , yes_votes no_votes, .
+10

:

select date(date) as date,
       sum(case when flag = 1 then 1 else 0) as yes,
       sum(case when flag = 2 then 1 else 0) as no
from post_votes
where date > '2010-07-01'
group by date(date)
+5

:)

IF SUM, :

SELECT SUM(IF(flag = 'yes',1,0)) AS yes_count,
       SUM(IF(flag = 'no',1,0)) AS no_count, 
       DATE(date) 
FROM post_votes 
WHERE date > '2010-07-01' 
GROUP BY DATE(date)

1 , yes/no

+4
SELECT DATE(date) as dt,
sum(if(flag=1,1,0)) as yes,
sum(if(flag=2,1,0)) as no
FROM post_votes WHERE date > '2010-07-01' 
GROUP BY dt
+4

I also had this problem. The best solution I can think of is to separate the “flag” in two fields, for example:

upvote(tinyint)
downvote(tinyint)

Then you can find them very light and without mysql-voodoo:

SELECT 
  SUM(upvote) AS up,
  SUM(downvote) AS down,
  DATE(`date`) AS Created_at
FROM post_votes 
WHERE Created_at > '2010-07-01' 
GROUP BY Created_at

Btw: You should not name the column datebecause it is a MySQL keyword.

0
source

More articles:


All Articles