Need help with Select count - 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

Transact-SQL (2000)

Need help with Select count

Author

Topic

ljp099
Yak Posting Veteran

79 Posts

Posted - 2006-04-29 : 12:38:58

I'm trying to display a list of the most downloaded articles. I have a DownloadedArticles table which has columns:

iduser, idarticle, title, datedownloaded

I want to order by the most downloaded article first as well as show a count of the number of downloads. Seems like it should be easy but I cant get the count.

Should display like this:

SQLTeam article #1 1009 downloads
SQLTeam article #2 888 downloads

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-29 : 18:37:56

[code]select idarticle, count(*) as total
from DownloadedArticles
group by idarticle
order by total desc[/code]

ljp099
Yak Posting Veteran

79 Posts

Posted - 2006-04-29 : 20:15:10

That gets a count, however, when I add the datedownloaded column, it gets a count for each article downloaded on the same dates.

If I run this statement:

SELECT SUM(UDH.count) AS totnum, D.Title, D.DocumentID
FROM UserDownloadHistory AS UDH INNER JOIN
Documents AS D ON UDH.articleDownloadedID = D.DocumentID
WHERE (UDH.articleDownloadedID = UDH.articleDownloadedID)
GROUP BY D.Title, D.DocumentID
ORDER BY totnum DESC

I get the correct count for the first article (931):

931 Training Measurement and Analytics: <i>Market Analysis</i> 10

If I simply add the column downloaddate and run this statement:

SELECT SUM(UDH.count) AS totnum, D.Title, D.DocumentID, UDH.downloadDateTime
FROM UserDownloadHistory AS UDH INNER JOIN
Documents AS D ON UDH.articleDownloadedID = D.DocumentID
WHERE (UDH.articleDownloadedID = UDH.articleDownloadedID)
GROUP BY D.Title, D.DocumentID, UDH.downloadDateTime
ORDER BY totnum DESC

I get a different, incorrect result:

129 Learning Content Management
Systems: <i>What Works</i> 181

What is the correct sql to show the count of the number of downloads for articles ordered by the most downloaded first?

thanks for any help

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-29 : 23:55:13

"What is the correct sql to show the count of the number of downloads for articles ordered by the most downloaded first?"
Each article will have mutliple download date ? which one do you want to show ?

Please post your table structure, some sample data and the expected result.

ljp099
Yak Posting Veteran

79 Posts

Posted - 2006-04-30 : 13:33:31

Users table
idUser | idOrg
104 | 20
222 | 24
311 | 32

UserDownloadHistory table
idUser | idDocument | downloadDate
104 | 3 | 02/20/2006
222 | 3 | 02/20/2006
311 | 3 | 01/08/2006
104 | 4 | 04/10/2006
222 | 4 | 04/10/2006
104 | 5 | 04/05/2006

Documents table
idDocument | Title
3 | Hamlet
4 | Othello
5 | Macbeth

Report Data should display:

#Downloads | Title | idDocument
3 | Hamlet | 3
2 | Othello | 4
1 | Macbeth | 5

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-01 : 00:24:57

[code]select count(*) as Downloads,
d.Title,
d.idDocument
from Documents d inner join UserDownloadHistory h
on d.idDocument = hidDocument
group by d.Title, d.idDocument[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources