Get every day of the week as MySQL and PHP columns

Question

I am writing a web application in PHP with MySQL.

I have a table called counts, and here is how the data is stored in this table:

  Table: counts

 id counts location_id media_id created_at
 --------------------------------------------------
 1 50 1 1 2017-03-15
 2 30 2 1 2017-03-15
 3 80 1 2 2017-03-15
 4 20 1 1 2017-03-16
 5 100 2 2 2017-03-16

For each unique location_id, media_id and created_at, I keep an account.

I have another table layout that looks like this:

  Table: locations

 id name
 ----------------
 1 Location 1
 2 Location 2
 3 Location 3
 4 Location 4
 5 Location 5

This is the SQL query that I have at the moment:

  select sum (counts.count) as views, locations.name as locations, DAYNAME (counts.created_at) AS weekday from `counts` inner join` locations` on `locations`.`id` =` counts`.`location_id` where `counts`.`created_at` between '2016-12-04' and '2016-12-10' group by` weekday`, `counts`.`location_id`;

Here's how the data is displayed:

 locations weekday views
 -----------------------------------
 Location 1 Mon 50
 Location 1 Tue 30
 Location 2 Mon 20
 Location 2 Tue 70

I am creating reports, and I would like to run the query so that all days of the week are displayed as a column with their corresponding values ​​as the number of views on that day of the week. I want something like this:

  locations mon tue wed thu fri sat sun
 -------------------------------------------------
 Location 1 40 60 51 20 40 20 30
 Location 2 80 60 100 24 30 10 5  

Is it possible that this is possible in MySQL or will I need to use PHP for this? If so, how do I do this?

Any help would be appreciated, thanks.

NB: sample data is not accurate.

Answer 1

It is possible to get this result with MySQL using conditional aggregation.

The trick is to use a conditional test in an expression in the SELECT list to determine if count should be returned.

Something like that:

SELECT l.name AS `locations` , SUM(IF(DATE_FORMAT(c.created_at,'%a')='Mon',c.count,0)) AS `mon` , SUM(IF(DATE_FORMAT(c.created_at,'%a')='Tue',c.count,0)) AS `tue` , SUM(IF(DATE_FORMAT(c.created_at,'%a')='Wed',c.count,0)) AS `wed` , SUM(IF(DATE_FORMAT(c.created_at,'%a')='Thu',c.count,0)) AS `thu` , SUM(IF(DATE_FORMAT(c.created_at,'%a')='Fri',c.count,0)) AS `fri` , SUM(IF(DATE_FORMAT(c.created_at,'%a')='Sat',c.count,0)) AS `sat` , SUM(IF(DATE_FORMAT(c.created_at,'%a')='Sun',c.count,0)) AS `sun` FROM `locations` l LEFT JOIN `counts` c ON c.location_id = l.id AND c.created_at >= '2016-12-04' AND c.created_at < '2016-12-04' + INTERVAL 7 DAY GROUP BY l.name ORDER BY l.name 

Note:

With the data samples, there are two lines for location_id=1 and created_at='2016-03-15' , so this query will return a total of 130 for tue (= 50 + 80), not 50 (as shown in the output example of an existing query).