The following Query provides the SQL to create a table that shows the number of entries by year and month. Some would call this a Pivot Table since it takes data that would look like this:
Month Year Number
January 2005 4
January 2004 3
February 2003 1
December 2004 4
etc.
and pivots or reformats it to look like
Month 2003 2004 2005
January 0 3 4
February 1 0 0
December 0 4 0
It could easily be modified to show other data or a different range of years. The sample below shows the number of entries for each month for the years 2003 to 2005.
<table border='1'>
<tr><th>Month</th><th>2003</th><th>2004</th><th>2005</th></tr>
{exp:query sql="SELECT
case
[code] when month = 1 then 'January'
when month = 2 then 'February'
when month = 3 then 'March'
when month = 4 then 'April'
when month = 5 then 'May'
when month = 6 then 'June'
when month = 7 then 'July'
when month = 8 then 'August'
when month = 9 then 'September'
when month = 10 then 'October'
when month = 11 then 'November'
when month = 12 then 'December'
end month_name,
sum( IF(year=2003,1,0) ) AS Y2003,
sum( IF(year=2004,1,0) ) AS Y2004,
sum( IF(year=2005,1,0) ) AS Y2005
FROM exp_weblog_titles
WHERE year >= 2003 and status='open'
GROUP BY month
ORDER BY month"}
<tr><td align='left'>{month_name}</th>
<td align='center'>{Y2003}</td><td align='center'>{Y2004}</td>
<td align='center'>{Y2005}</td></tr>
{/exp:query}
</table>
The results would look something like:
Of course, all months would appear if you have data for them.
For more information about using SQL to create pivot tables and other common queries visit: http://www.artfulsoftware.com/queries.php
Category:Queries Category:Templates Category:Archives