download stats - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

download stats

Author

Topic

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2007-03-09 : 21:55:38

hi all,

This really should not be a problem but I have 2 issues I can't seem to get right.

I have a table call Downloads with the following field names:

DownloadID - identity
Title - nvarchar, 255
FileName - nvarchar, 255
Downloads - int
DateDownloaded - Date/Time

I wanted to display 3 different types of results:

1, Most view file
2, Daily File views
3, Nost Viewed by Date

My query for the first one:

Select filename, MAX(Downloads) from Downloads

I was look for results like:

File Name Total View
File1 35

Just the most viewed file, nothing else

The second one, my query looks like this:

Select downloadDate, file, title, count(downloads) from downloads

I expected results like:

File Name Title Date # of downloads
file1 turtle ninja 03/08/2007 3
file2 Pebble Beach 03/08/2007 2
etc
etc

Lastly, I want to know most viewed by date.

I didn't try this since the first 2 weren't producing the correct results.

Any assistance for any or all would be greatly, greatly appreciated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-09 : 23:45:56

1. Most view file


select *
from   Downloads d 
where  d.downloads = (select max(downloads) from Downloads)

2. Daily File views


selet filename, title, downloadDate, count(downloads) as [total downloads]
from  downloads
group by filename, title, downloadDate,

3. Most Viewed by Date


select downloadDate, filename, downloads 
from   Downloads d
where  filename = (select top 1 filename from downloads x where x.downloaddate = d.downloaddate order by downloads desc)

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2007-03-10 : 08:51:35

hi Khtan and thanks for your response.

I am not getting the result I am expecting.

I was hoping to see something like:

Most Downloaded / Viewed File

File Name # of times Downloaded / Viewed
Ninja 5

Instead all the files are listed.

For instance, there are 3 files downloaded (just for testing)

2 are downloaded once each and the 3rd has been downloaded 5 times.

Instead all 3 files are listed and the one that is downloaded 5 times is listed 5 times.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-10 : 08:55:31

it will be easier if you can provide the table structure, sample data and the expected result

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2007-03-10 : 10:07:16

ok,

Here is the table structure and it is the same I provided when I first posted this question:

DownloadID - identity
Title - nvarchar, 255
FileName - nvarchar, 255
Downloads - int
DateDownloaded - Date/Time

typical data:

Download ID Title File Name Downloads Date Downloaded
1 title1 file1 1 2/25/2007
2 title1 file1 1 2/25/2007
3 title1 file1 1 2/25/2007
4 title2 file2 1 2/25/2007
etc
etc

Now, if I need to know the most downloaded, I was hoping to show that file1 is the most downloaded = downloaded 3 times

If I need to change the structure a little bit, can you please tell me what to change?

Thanks for your assistance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-10 : 18:12:56

most downloaded :


select top 1 FileName, total_downloads = sum(Downloads)
from   Downloads
group by Filename
order by total_downloads desc

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2007-03-10 : 22:11:12

Thank you very much, that worked!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources