0

I'm trying to dynamically use the dates within the date range of the where clause as the column names in the results. I know I can hard code this using a case/when statement:

sum(case when day(date_accessed) = 1 THEN 1 ELSE 0 END) AS `Jan 1 2014`...

Doing this every month will be cumbersome. I also know I can use php or some other scripting language to build a table, but I'm trying to accomplish all of this in mysql. Here's the query I have. 

SELECT count(*) AS `Total Clicks`, day(date_accessed)
FROM Client_Activity_Log a WHERE date_accessed between '2014-01-01' AND '2014-01-31'
GROUP BY day(date_accessed);

The above query will put each date into it's own row and total it, but I'd like each date to be column. I want the results to look like this (all days of month):

Jan 1 2014      Jan 2 2014
150             200
Chris
  • 5,882
  • 2
  • 32
  • 57
user3411972
  • 3
  • 1
  • 2
    Consider handling issues of data display in the presentation layer/application-level code, assuming you have that (e.g. a simple PHP loop acting on an ordered array) – Strawberry Mar 12 '14 at 17:51
  • 2
    Even if you managed to do that, it will presumably lead straight to your next problem – _“I want such columns with value 0 as well for dates for which no entry even exists”_ (I assume you will want that, right?) … and there you are in the realm of “creating data that is not even there”, which with a normal query will get quite complicated. You really are better off with doing this afterwards in your processing of the data IMHO. – CBroe Mar 12 '14 at 17:54
  • Don't put the answer in the question. Also, this has been answered before: – Marcus Adams Mar 12 '14 at 18:04
  • possible duplicate of [MySQL pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table) – Marcus Adams Mar 12 '14 at 18:05
  • Normally I wouldn't put the answer in the question but it wouldn't let me answer my own question within 8 hours of poting. I'll check out the pivot table. – user3411972 Mar 13 '14 at 00:43

1 Answers1

0

I may have found a decent solution. Let me know if anyone has a better one:

I may have figured out the easiest solution. It won't provide the month or year in the column name but it may be as close as I get:

SELECT 
SUM(CASE WHEN day(date_accessed) = 1 THEN 1 ELSE 0 END) AS '1', 
SUM(CASE WHEN day(date_accessed) = 2 THEN 1 ELSE 0 END) AS `2`, 
SUM(CASE WHEN day(date_accessed) = 3 THEN 1 ELSE 0 END) AS `3`, 
SUM(CASE WHEN day(date_accessed) = 4 THEN 1 ELSE 0 END) AS `4`, 
SUM(CASE WHEN day(date_accessed) = 5 THEN 1 ELSE 0 END) AS `5`, 
SUM(CASE WHEN day(date_accessed) = 6 THEN 1 ELSE 0 END) AS `6`, 
SUM(CASE WHEN day(date_accessed) = 7 THEN 1 ELSE 0 END) AS `7`, 
SUM(CASE WHEN day(date_accessed) = 8 THEN 1 ELSE 0 END) AS `8`, 
SUM(CASE WHEN day(date_accessed) = 9 THEN 1 ELSE 0 END) AS `9`, 
SUM(CASE WHEN day(date_accessed) = 10 THEN 1 ELSE 0 END) AS `10`, 
SUM(CASE WHEN day(date_accessed) = 11 THEN 1 ELSE 0 END) AS `11`, 
SUM(CASE WHEN day(date_accessed) = 12 THEN 1 ELSE 0 END) AS `12`, 
SUM(CASE WHEN day(date_accessed) = 13 THEN 1 ELSE 0 END) AS `13`, 
SUM(CASE WHEN day(date_accessed) = 14 THEN 1 ELSE 0 END) AS `14`, 
SUM(CASE WHEN day(date_accessed) = 15 THEN 1 ELSE 0 END) AS `15`, 
SUM(CASE WHEN day(date_accessed) = 16 THEN 1 ELSE 0 END) AS `16`, 
SUM(CASE WHEN day(date_accessed) = 17 THEN 1 ELSE 0 END) AS `17`, 
SUM(CASE WHEN day(date_accessed) = 18 THEN 1 ELSE 0 END) AS `18`, 
SUM(CASE WHEN day(date_accessed) = 19 THEN 1 ELSE 0 END) AS `19`, 
SUM(CASE WHEN day(date_accessed) = 20 THEN 1 ELSE 0 END) AS `20`, 
SUM(CASE WHEN day(date_accessed) = 21 THEN 1 ELSE 0 END) AS `21`, 
SUM(CASE WHEN day(date_accessed) = 22 THEN 1 ELSE 0 END) AS `22`, 
SUM(CASE WHEN day(date_accessed) = 23 THEN 1 ELSE 0 END) AS `23`, 
SUM(CASE WHEN day(date_accessed) = 24 THEN 1 ELSE 0 END) AS `24`, 
SUM(CASE WHEN day(date_accessed) = 25 THEN 1 ELSE 0 END) AS `25`, 
SUM(CASE WHEN day(date_accessed) = 26 THEN 1 ELSE 0 END) AS `26`, 
SUM(CASE WHEN day(date_accessed) = 27 THEN 1 ELSE 0 END) AS `27`,
SUM(CASE WHEN day(date_accessed) = 28 THEN 1 ELSE 0 END) AS `28`,
SUM(CASE WHEN day(date_accessed) = 29 THEN 1 ELSE 0 END) AS `29`,
SUM(CASE WHEN day(date_accessed) = 30 THEN 1 ELSE 0 END) AS `30`,
SUM(CASE WHEN day(date_accessed) = 31 THEN 1 ELSE 0 END) AS `31`
FROM Client_Activity_Log a WHERE date_accessed between '2014-01-01' AND '2014-01-31';
user3411972
  • 3
  • 1