Complicated query

[Puiu]

I have the following tables in my database:

 

Media(IdMedia, Description)

Images(IdImage, IdMedia, Size)

Requests(IdRequest, IdMedia, DownloadDate, Aproved)

 

I need to create a query that will calculate the total downloads of each image and show the user the percentage of those downloads in the total number of downloads in the current month

Example:

Let�s say we have 4 downloads this month: 2 for the idmedia=1 and one for idmedia=2 and idmedia=3. The final table should show me something like this:

 

Idmedia Description Percent

1 Picture1 50

2 Picture2 25

3 Picture3 25

 

I consider an image downloaded when Requests.Aproved=�D�

So far I�ve managed to create a query but it�s rather long�maybe there is a simpler way:

 

select idmedia, description, (convert(numeric(5,2), totaldownloads)/(select convert(numeric(5,2), sum(totaldownloads)) from (select m.Description, m.idmedia, count(*) as TotalDownloads

from media m, images i, requests r

where m.idmedia=i.idmedia AND r.idmedia=m.idmedia AND r.aproved='D' and month(r.downloaddate)=datepart(month, getdate())

group by m.Description, m.idmedia

) as t1 ))* 100 as totalPercent

from

(

select m.Description, m.idmedia, count(*) as TotalDownloads

from media m, images i, requests r

where m.idmedia=i.idmedia AND r.idmedia=m.idmedia AND r.aproved='D' and month(r.downloaddate)=datepart(month, Getdate())

group by m.Description, m.idmedia

) as t1

GROUP BY idmedia, description, totaldownloads

 

Thanx

[Afraits]

Not that I'm aware of. I had to use a similar method for a similar problem a few months back.