CODEWITHSUNDEEP
mysqlsqlwordpress
KodeFor.Me
KodeFor.Me

Reputation: 13511

MySQL Group By assistance need it

I try to craete a randomized banner plugin for my WordPress installation, and also I like to extract some statistic information from my banners.

To perform that task I have create a table with the following signature:

CREATE TABLE `bb_banner_statistics` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) DEFAULT NULL,
  `event` varchar(15) DEFAULT NULL,
  `value` tinyint(4) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1

where post_id is the banner ID, event is the event I like to track (ie. viewed, hovered, clicked, etc), value just take the value "1" (I did this for easy sumirization of the specific event type), and created that is the date time of the event.

So, in example the table can contain values like the following:

|---------------------------------------------------|
| ID | POST_ID | EVENT | VALUE | CREATED            |
|---------------------------------------------------|
| 1  | 62      | view  | 1     | 2014-4-12 11:45:12 |
| 2  | 63      | view  | 1     | 2014-4-12 11:45:12 |
| 3  | 64      | view  | 1     | 2014-4-12 11:45:12 |
| 4  | 62      | hover | 1     | 2014-4-12 11:46:18 |
| 5  | 63      | hover | 1     | 2014-4-12 11:46:22 |
| 6  | 63      | click | 1     | 2014-4-12 11:46:23 |
| 7  | 62      | hover | 1     | 2014-4-12 11:46:23 |
| 8  | 62      | view  | 1     | 2014-4-13 09:20:17 |
|---------------------------------------------------|

So the question is, how can I group my data in order to get the total views performed for each banner for each day, the total clicks and the total hover events ?

Upvotes: 0

Views: 37

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270793

I think you want conditional aggregation:

select date(created) as thedate, post_id,
       sum(event = 'click') as numclicks,
       sum(event = 'view') as numviews,
       sum(event = 'hover') as numhovers
from bb_banner_statistics
group by date(created), post_id;

Upvotes: 2

Related Questions