SQL query help

julesmel · October 20th, 2003, 07:42 AM

OK, this may be easy to do or it may be impossible but I really don't know how to do this.

Basically, I have just one table which is the output from a log file. I want to count the number of entries for each day.

I can do the following :-

SELECT COUNT(*) FROM log

This gives me the count for every day. I can also do:-

SELECT DISTINCT CAST(CONVERT(CHAR(10), logDate, 101) AS datetime FROM log

which gives me each date. My question is:-

How do I combine these two queries to give me the number of log entries for each distinct day ?

Jonax · October 20th, 2003, 07:47 AM

I think this'll do it:

SELECT DISTINCT CAST(CONVERT(CHAR(10), logDate, 101) AS datetime, COUNT(*) FROM log GROUP BY CAST(CONVERT(CHAR(10), logDate, 101)

Jeff Mason · October 20th, 2003, 07:50 AM

COUNT(*) counts the number of rows in each GROUP BY group. If you do not have a GROUP BY clause, then the entire query is considered to be one big group, which is why COUNT(*) gives the number of rows in the query.

Thus, you want to GROUP by the date, as:

SELECT TheDate, COUNT(*) FROM table GROUP BY TheDate

To group by your expression, your query will be a bit ugly, as you will have to repeat the expression in both the SELECT and the GROUP BY clause:

Code:

SELECT CAST(CONVERT(CHAR(10), logDate, 101) as TheDate, COUNT(*) FROM Log
GROUP BY CAST(CONVERT(CHAR(10), logDate, 101)
ORDER BY TheDate

Note that you do not have to repeat the expression in an ORDER BY clause, as any column alias's are visible to the ORDER BY clause (but not the GROUP BY clause).

P.S. Don't use a datatype name such as 'datetime' as a column name - eventually you'll regret it.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

julesmel · October 20th, 2003, 08:22 AM

Jeff, Jonax,

You are both right and the query now works wonderfully.

Didn't think of the GROUP BY clause, obvious now though.

Thanks for the help.