Results 1 to 3 of 3

Thread: Group by alias

  1. #1
    Join Date
    Nov 2009
    Posts
    1

    Unanswered: Group by alias

    Hey,

    i want to form a selection where i can group by an alias:


    Code:

    select count(nr),to_char(dat, '%Y') as year
    from table
    where dat between "2005-01-01" and "2009-12-31" and nr=1
    group by year

    I´d like to build a group for each year to get an yearwise count

    But informix doesn´t allow me to use such a group

  2. #2
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    Why not? If I make your query like:
    Code:
    SELECT to_char(dat, '%Y') year, count(*) number
    FROM table                                     
    WHERE dat BETWEEN '2005-01-01' AND '2009-12-31'
    AND nr = 1                                     
    GROUP BY 1
    I get the result you described.

    Regards,
    Hans

  3. #3
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    Unfortunately this syntax isn't supported... must copy all column or use number like Tyvelen suggest
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

Posting Permissions

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