Different way to make an Aggregated Query

Question

I currently have a table where I am tracking a bunch of events tracking the origin of such events. I am currently creating a report by company summarizing all the different origins of such events.

The table seems as follows

-------------------------------------
| company_id |  source  |    date   |
-------------------------------------
| 156012     | facebook | 2016-08-22|
| 156012     | twitter  | 2016-08-22|
| 156012     | facebook | 2016-08-12|
| 160781     | twitter  | 2016-07-20|
| 160781     | twitter  | 2016-07-20|

I have omitted several fields and that's just sample data. I am gathering a report that counts the events by source to each company. I would like my report to look something like this

| company_id | facebook | twitter  |
------------------------------------
| 156012     |     2    |     1    |
| 150781     |     2    |     0    |

Currently I am doing it this way

select company_id,
  sum(if(track.source = 'facebook', 1,0)) as facebook,
  sum(if(track.source = 'twitter', 1,0)) as twitter,
from event_tracks as track
group by franchise_id
order by total_clicks desc limit 10;

I Would like to know if there's a better way to do this as the source field might take a wide range of values, so the report won't be a nice fit for larger data.

I think a better way is to handle issues of data display like this in application code — Strawberry, Aug 13 '17 at 21:12
Sometimes that might work, but I am not sure if this is always the case as sometimes data is meant not only for display purposes — Taro, Aug 13 '17 at 21:27
You're gathering the data for a report. Under the circumstances, I really can't see when the solution I've proposed would be inappropriate. — Strawberry, Aug 14 '17 at 05:58

score 1 · Answer 1 · answered Aug 13 '17 at 22:06

1

The if condition is redundant as mysql already evaluates booleans to a 1 or 0. Also, did you mean to select franchise_id, which is the key you're grouping by, as opposed to company_id?

Unless you don't care which value from a group is chosen, you should only select columns in your group by:

select franchise_id,
  sum(track.source = 'facebook') as facebook,
  sum(track.source = 'twitter') as twitter,
from event_tracks as track
group by franchise_id
order by total_clicks desc limit 10;

answered Aug 13 '17 at 22:06

FuzzyTree

32,014
3
54
85

This is a nice improvement though the query seems much cleaner – Taro Aug 13 '17 at 22:37
This doesn't address the question – Strawberry Aug 14 '17 at 05:59

Different way to make an Aggregated Query

1 Answers1