COUNT and GROUP help (newbie) 2

[Trope]

I am logging hits to my site in a database.

How would I display the most popular pages visited ( other than default.asp ) and how would I display the LEAST visited pages? I am a newbie but I think it's some sort of count and group?

my database looks like this:

table: pagelog

page_name | date_visited
---------------------------------
somepage.asp | 1/2/2001

So I need to count the page_name column I guess?

Any advice would be greatly appreciated. Thanks.

John

 

[fluteplr]

select page_name, count(*) from pagelog
group by page_name
order by count(*)


or

order by count(desc)

if your database supports top you can use something like the following

select top 10 page_name, count(*) from pagelog
group by page_name
order by count(*)

 

[angiole]

Or, you can get cute by combining top and bottom results at once for reporting:

select TOP 10
'Top 10 pages are :',
page_name,
count(*)
from pagelog
group by 'Top 10 pages are :',
page_name
order by count(*) ASC
UNION ALL
select BOTTOM 10
'Bottom 10 pages are :',
page_name,
count(*)
from pagelog
group by 'Bottom 10 pages are :',
page_name
order by count(*) DESC


Your syntax may vary ...

AA 8~)


AA 8~)

 

[r937]

yeah, your sql will have to vary

you can only have one ORDER BY in a UNION

i never heard of BOTTOM (i'm not sure TOP is standard either, but i could be wrong)

i dunno about grouping on a literal -- i suppose it might be syntactially okay, but why would you want to?

;o)

rudy

http://rudy.ca/