If you are on MSSql 2K then this is the solution:
select count(convert(varchar, creationdate, 101)), convert(varchar,
creationdate, 101)
from referer
group by convert(varchar, creationdate, 101)
Steve
-----Original Message-----
From: Che Vilnonis [mailto:[EMAIL PROTECTED]
Sent: Friday, March 12, 2004 2:52 PM
To: CF-Talk
Subject: RE: Quick SQL Question...
mark, i see where you are coming from...alas, i still am having no luck.
-----Original Message-----
From: Mark A. Kruger - CFG [mailto:[EMAIL PROTECTED]
Sent: Friday, March 12, 2004 2:51 PM
To: CF-Talk
Subject: RE: Quick SQL Question...
The 2 are mutually exclusive. One is the "group" of all the stuff on this
day, but the other (the actual date) is not merely a date - but a - by
nature a very unique item - down to the second. So you are actually
getting
the grouping you are asking for. If 2 referals occured at the exact
instant
(say '3/4/2004 5:07:35 PM') ... then you would see "2 3/4/2004 5:07:35
PM" -
but since that's unlikey this is more or less like doing a "select *
from.....". What you need is a label and an order by clause:
SELECT COUNT(creationdate) AS TotalRefsPerDay,
datepart(month,creationdate)
+ '-' + datepart(day,creationdate) + '-' + datepart(year,creationdate) AS
datelabel
group by datepart(dw,creationdate)
order by datelabel
or something similar. The DateName( ) function would work as well - maybe
better. It usally takes some work to get it exactly right.
-Mark
Mark A. Kruger, MCSE, CFG
www.cfwebtools.com
www.necfug.com
http://blog.mxconsulting.com
...what the web can be!
-----Original Message-----
From: Che Vilnonis [mailto:[EMAIL PROTECTED]
Sent: Friday, March 12, 2004 1:28 PM
To: CF-Talk
Subject: Quick SQL Question...
This SQL Code works:
SELECT COUNT(creationdate) AS TotalRefsPerDay
FROM Referer
GROUP BY DATEPART(dw, creationdate)
197
364
350
294
260
243
196
But when I add a column... this does not):
SELECT COUNT(creationdate) AS TotalRefsPerDay, creationdate
FROM Referer
GROUP BY DATEPART(dw, creationdate), creationdate
1 3/4/2004 5:07:35 PM
1 3/4/2004 5:09:27 PM
1 3/4/2004 5:11:19 PM
1 3/4/2004 5:13:14 PM
1 3/4/2004 5:14:31 PM
1 3/4/2004 5:14:53 PM
1 3/4/2004 5:16:19 PM
1 3/4/2004 5:20:37 PM
1 3/4/2004 5:21:10 PM
What am I doing wrong? I'd like a simple two column table: creation date
and the total number of referers for each day!
TIA...Che
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
