SQL help

[thoand]

Hi,
I have a SQL table which stores downloads of PDF documents
document, download_date etc.

I want to count the number of downloads of each of the last 12 months.

Is that posible using one query +code?

I think it should, but are not able to do any drawing at the moment.

can anyone help,

thanks in advance
regards Thomas

[barand]

Try

$yearago = date('Y-m-d', strtotime("-12 months"));

$sql = "SELECT document, COUNT(*) as downloads FROM pdf_documents
WHERE download_date >= '$yearago'
GROUP BY document";

hth

[nemonoman]

Here's how to get the downloads by month:

SELECT DATE_FORMAT(download_date,'%Y - %m'), sum(downloads)
WHERE download_date BETWEEN CURRENT_DATE() - INTERVAL 1 YEAR AND CURRENT_DATE()
GROUP BY DATE_FORMAT(download_date,'%Y - %m')
ORDER BY DATE_FORMAT(download_date,'%Y - %m')

Will output something like

2004-06 20
2004-05 15
2004-04 22

etc

You can add a title field, etc.
SELECT DATE_FORMAT(download_date,'%Y - %m'), sum(downloads), title
WHERE download_date BETWEEN CURRENT_DATE() - INTERVAL 1 YEAR AND CURRENT_DATE()
GROUP BY DATE_FORMAT(download_date,'%Y - %m'), title
ORDER BY DATE_FORMAT(download_date,'%Y - %m')

Will output something like

2004-06 4 title1
2004-06 3 title2
2004-05 15 title1
2004-05 13 title2

etc

etc