Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2002
    Posts
    98

    Unanswered: group by with datetime datatype

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    did you actually define a column called date? that's a reserved word and may be the source of your problem

    rudy

  3. #3
    Join Date
    Nov 2002
    Posts
    98
    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);

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, i've never actually used informix, but i know where the docs are

    try GROUP BY 1

    see Using Select Numbers

    rudy

  5. #5
    Join Date
    Feb 2002
    Posts
    2,232
    You can also do it by derived columns -

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

  6. #6
    Join Date
    Nov 2002
    Posts
    98
    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

  7. #7
    Join Date
    Feb 2002
    Posts
    2,232
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •