Group by alias

der_steph · #1 (**permalink**) 11-30-09, 11:45

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

Tyveleyn · #2 (**permalink**) 11-30-09, 17:30

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

ceinma · #3 (**permalink**) 12-01-09, 13:48

Unfortunately this syntax isn't supported... must copy all column or use number like Tyvelen suggest

Internet Services

Web Development

Digital Marketing

Consumer Tech