i got several downloads stored in table datei
i have datei_id as primary key, dateiname as filename and some other data
furthermore i have a table download
download_id primary key
datei_id
download_date
now my customer wants to be able to see which files have been download in the current month and how often those files have been downloaded in total
this of course is possible doing two queries:
/* downloads this month */
SELECT
d.datei_id, d.dateiname, COUNT( m.datei_id ) downloads
FROM
download m
INNER JOIN datei d ON d.datei_id = m.datei_id
WHERE
DATE_FORMAT( m.datum, '%Y%m' ) = '200311'
GROUP BY d.datei_id
/*downloads total*/
SELECT
d.datei_id, d.dateiname, COUNT( m.datei_id )
FROM
ba_download m
INNER JOIN ba_datei d ON d.datei_id = m.datei_id
WHERE
d.datei_id IN (here the datei_id values retrieved from the statement above are listed)
GROUP BY d.datei_id
my question is: is it possible to do this havng one SELECT only?
