Selecting the most visited days

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)

Selecting the most visited days

Author

Topic

fizgig
Starting Member

34 Posts

Posted - 2001-12-14 : 04:04:57

Hi there.

I have a table where i register every page view on my website :

id, articleid, moment (datetime field)

The select statement i'm looking for should compose a list of pages visited by day. Got an idea?

Nazim
A custom title

1408 Posts

Posted - 2001-12-14 : 05:04:24

if u r looking for page on a specific day

this should help

select id, articleid,moment from tablename
where moment='14-dec-2001'

am sure u r looking for something more, can u xplain more

-------------------------
"Success is when Preparedness meets Opportunity"

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-12-14 : 05:33:17

Or did you mean something more like this?


SELECT CONVERT(varchar, moment, 112), articleid, COUNT(*)
FROM pageviews
GROUP BY CONVERT(varchar, moment, 112), articleid
ORDER BY CONVERT(varchar, moment, 112), COUNT(*) DESC, articleid

fizgig
Starting Member

34 Posts

Posted - 2001-12-14 : 10:43:01

Nop,

I would like to get the most popular days

example output

01/01/2001 23232 Hits
01/02/2001 32222 Hits

etc etc

quote:

Or did you mean something more like this?


SELECT CONVERT(varchar, moment, 112), articleid, COUNT(*)
FROM pageviews
GROUP BY CONVERT(varchar, moment, 112), articleid
ORDER BY CONVERT(varchar, moment, 112), COUNT(*) DESC, articleid

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2001-12-14 : 11:21:40

select convert(moment, char(10), 101), count(id)
from tablename
group by convert(moment, char(10), 101)
order by convert(moment, char(10), 101)

Jeff Banschbach
Consultant, MCDBA

fizgig
Starting Member

34 Posts

Posted - 2001-12-14 : 11:33:23

This code throws an error unfortunately ;-(

Server: Msg 164, Level 15, State 1, Line 1
GROUP BY expressions must refer to column names that appear in the select list.

quote:

select convert(moment, char(10), 101), count(id)
from tablename
group by convert(moment, char(10), 101)
order by convert(moment, char(10), 101)

Jeff Banschbach
Consultant, MCDBA

PiecesOfEight
Posting Yak Master

200 Posts

Posted - 2001-12-14 : 11:39:55

Does aliasing help?

select convert(moment, char(10), 101) as colname, count(id)
from tablename
group by colname
order by convert(moment, char(10), 101)

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2001-12-14 : 11:44:03

That's because I'm suffered a bout of syntax dyslexia.... should read like so:

select convert(char(10), moment, 101), count(id)
from tablename
group by convert(char(10), moment, 101)
order by convert(char(10), moment, 101)

Jeff Banschbach
Consultant, MCDBA

fizgig
Starting Member

34 Posts

Posted - 2001-12-14 : 14:41:24

Give the man a beer! It Worx, TNX!

quote:

That's because I'm suffered a bout of syntax dyslexia.... should read like so:

select convert(char(10), moment, 101), count(id)
from tablename
group by convert(char(10), moment, 101)
order by convert(char(10), moment, 101)

Jeff Banschbach
Consultant, MCDBA