I am writing a web application in PHP with MySQL.
I have a table called counts and this is how data is stored in that 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 every unique location_id, media_id and created_at, I store count.
I have another table locations which is 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 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`;
This is 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'm creating reports and I would like to run a query such that all the days of the week appear as a column with their corresponding values as the view count for 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 the above possible in MySQL or I would have to use PHP to achieve that? If so, how do I go about it?
Any help will be appreciated, thanks.
NB: The sample data is not accurate.
WEEKDAY() function in MySQL is used to find the weekday value for a given date. If the date is NULL, the WEEKDAY() function will return NULL. Otherwise, it returns index for a date i.e., 0 for Monday, 1 for Tuesday, … 6 for Sunday.
The DAY() function returns the day of the month for a given date (a number from 1 to 31). Note: This function equals the DAYOFMONTH() function.
Explanation: weekday(now()) returns the current weekday (starting with 0 for monday, 6 is sunday). Subtract the current weekday from 6 and get the remaining days until next sunday as a result. Then add them to the current date and get next sunday's date.
The best way to create a pivot table in MySQL is using a SELECT statement since it allows us to create the structure of a pivot table by mixing and matching the required data. The most important segment within a SELECT statement is the required fields that directly correspond to the pivot table structure.
MySQL WEEKDAY Function MySQL Functions. Example. Return the weekday number for a date: SELECT WEEKDAY("2017-06-15"); Try it Yourself » Definition and Usage. The WEEKDAY() function returns the weekday number for a given date. Note: 0 = Monday, 1 = Tuesday, 2 = Wednesday, 3 = Thursday, 4 = Friday, 5 = Saturday, 6 = Sunday.
You can use the DAYOFWEEK () function in MySQL to return the day of the week from a date. In this context, a return value of 1 corresponds to Sunday, 2 corresponds to Monday, etc. This article contains examples to demonstrate.
The DAYOFWEEK() function returns the weekday index for a given date (a number from 1 to 7). Note: 1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday, 6=Friday, 7=Saturday.
The DAYOFMONTH () function returns a value between 1 and 31 (or 0 for dates with a zero day part) that represents the day of the month. It resets back to 1 at the start of each month. The DAYOFWEEK () function on the other hand, returns a value between 1 and 7. It resets back to 1 at the start of each week.
It's possible to achieve this result with MySQL, using conditional aggregation.
The trick is to use a conditional test in an expression in the SELECT list to determine whether to return a value of count.
Something like this:
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 sample data, there are two rows for location_id=1 and created_at='2016-03-15'
, so this query would return a total of 130 for tue
(=50+80), not 50 (as shown in the sample output of the existing query).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With