0

I have a table which records various information about tasks done by IDs. The table looks like:

ID Mark ------Date
1    50 -----03/08/2012
1    60 -----01/07/2012
2    40 -----05/08/2012
2    50 -----02/07/2012
.... etc

What I want to do, is have the outcome as this:

ID Marks_In_Jul ---Marks_In_Aug
1  -------60 -----------------50       
2  -------50 -----------------40

.... etc (where columns can be split into months, weeks, days if needed)

I can do this manually, by doing this:

SELECT 
id,
sum(if(date BETWEEN '2011-07-01' AND '2011-07-31', mark,0)) as Marks_In_Jul,
sum(if(date BETWEEN '2011-08-01' AND '2011-08-31', mark,0)) as Marks_In_Aug,
...
GROUP BY id;
.... etc

but this is all manual, and if i want to narrow it down to weeks it takes time to change the dates manually (and its almost impossible by day)

Is there a way, to make some sort of loop or something similar in MySQL, where i could change starting/ending values and the required gap in days (either 1, 7 or 30 etc...)

Thank you, Don

phemark
  • 3
  • 1
  • 5
  • Use stored procedures: http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html You can have those dates as input parameters. – Nathan Aug 03 '12 at 14:16
  • Regarding Pivot Tables, check: http://www.artfulsoftware.com/infotree/queries.php#78 and http://stackoverflow.com/questions/7674786/mysql-pivot-table – Nathan Aug 03 '12 at 14:19

0 Answers0