Select daily amount of specific values from same column

Question

I'm building a feedback tool, and I have a feedback table that the following structure:

ID  |  Satisfaction  |  Timestamp
--------------------------------------------
 1                0     2014-01-01 00:00:00
 2                5     2014-01-01 00:00:00
 3               10     2014-01-02 00:00:00
 4                5     2014-01-02 00:00:00
 5               10     2014-01-03 00:00:00
 6                0     2014-01-03 00:00:00
 7               10     2014-01-03 00:00:00
 8                5     2014-01-04 00:00:00
 9                5     2014-01-04 00:00:00

How can I get a daily count of the number of each "satisfaction" value?

For example:

Date        |  0's  |  5's  |  10's          
--------------------------------------
2014-01-01  |  1    |  1    |  0
2014-01-02  |  0    |  1    |  1
2014-01-03  |  1    |  0    |  2
2014-01-04  |  0    |  2    |  0

I'd imagine it involves a GROUP BY timestamp, but I'm not sure how to select

score 3 · Answer 1 · answered Dec 30 '14 at 17:08

3

The simplest way to pivot this data in MySQL:

select date(timestamp),
       sum(satisfaction = 0) as zeroes,
       sum(satisfaction = 5) as fives,
       sum(satisfaction = 10) as tens
from feedback
group by date(timestamp);

answered Dec 30 '14 at 17:08

Gordon Linoff

1,242,037
58
646
786

score 0 · Accepted Answer · answered Dec 30 '14 at 17:06

Solved! I was able to grab the counts of the individual values using a combination of sum() and case statements.

SELECT
  DATE(timestamp),
  IFNULL(sum(case when satisfaction = 0 then 1 end), 0) as 'unhappy',
  IFNULL(sum(case when satisfaction = 5 then 1 end), 0) as 'neutral',
  IFNULL(sum(case when satisfaction = 10 then 1 end), 0) as 'happy' 
FROM feedback
GROUP BY DATE(timestamp)

Select daily amount of specific values from same column

2 Answers2