I am trying to see a list of total log records, grouped by month:

select month(date),count(*)
from playcount
where year(date) = year(today)
group by month(date);

this gives me a syntax error
it appears the group by statement is not valid
i tried just "group by date", it worked but it's not what i wanted.

any suggestions?

TIA

Mark

did you actually define a column called date? that's a reserved word and may be the source of your problem

rudy

doesn't appear to be the case (tho your point is valid... i would never use date as an attribute name :P)

the following query also has a syntax error:

select month(playdate), count(*) from daily_prog
where year(playdate)=year(today)
group by month(playdate);

well, i've never actually used informix, but i know where the docs are

try GROUP BY 1

see Using Select Numbers

rudy

You can also do it by derived columns -

select month(playdate) test1, count(*) from daily_prog
where year(playdate)=year(today)
group by test1;

you guys are great help!

tho for some reason rnealejr's solution would not work. It seems informix doesn't recognize alias in group by clause either.

Mark

You are correct - for some reason I thought you were using an order by. Derived columns work for order by but not for group by.