GROUP BY expression

[rac2]

What is the a problem with this query?

SELECT
	HOUR(AcctStartTime),
	COUNT(*)
FROM radacct
WHERE
	AcctStartTime BETWEEN '2003-11-03 00:00:00' AND '2003-11-03 23:59:59'
GROUP BY HOUR(AcctStartTime)

Executing this in an ASP script using ADODB with an unknown version of MySQL. Other queries work, including GROUP BY queries; but none of the date functions, DAYOFYEAR, MONTH, etc can be used with GROUP BY. These functions work in simple SELECT queries.

The error message is
You have an error in your SQL syntax near '(AcctStartTime) ' at line 1.


Could this be a limitation of an old version of MySQL?

 

[hos2]

perhaps you should try

SELECT
    HOUR(AcctStartTime) as hourtime,
    COUNT(*)
FROM radacct
WHERE
    AcctStartTime BETWEEN '2003-11-03 00:00:00' AND '2003-11-03 23:59:59'
GROUP BY hourtime


or

SELECT
    max(HOUR(AcctStartTime)),
    COUNT(*)
FROM radacct
WHERE
    AcctStartTime BETWEEN '2003-11-03 00:00:00' AND '2003-11-03 23:59:59'
GROUP BY HOUR(AcctStartTime)

since the groupy by field is obvious the same for you I think sql won't see it that way so just as every other field the result can be unknown and has to be forced to a certain value.

hope this will work

 

[hos2]

but I find the count(*) a very strange option if I may say so. why not a fieldname in it ??

 

[rac2]

The intention of this query is to breakdown starting time by hour of the day. In other words, how many activities started during each hour of the day.

COUNT(*) is an expression for counting rows. COUNT(fieldname) counts rows with a non-null value in a field, which is a different thing.

Adding a second aggregate function, MAX(), and using the same GROUP BY HOUR(AcctStartTime) works; but why and what does it mean? The maximum value of HOUR() is the value of HOUR(), so that is not too satisfactory as far as the meaning of the query. It works but it is ugly.

Defining an alias for HOUR(AcctStartTime) and using it in the GROUP BY clause works. Thanks for the idea.

 

[hos2]

fot the max field and why to use it I give another example

name, town
arthur,amsterdam
henk,amsterdam
piet,amsterdam

if I do
select name,town from adresses group by town

then an error can occur since it is undefined which fieldvalue it has to show for name since all 3 are right in this query.

therefore the max, min or other funtion is used to force an certain value
select max(name),town from adresses group by
town


although you think youre querie works on the same field in the group by and select. but that's nog the case since you use a function to alter the value and the group by doesn't know that there is the same value in the select. so you have to either make an alias or force a certain value with a function like max,min etc

 

[rac2]

Do you think that HOUR() is an aggregate function?