Jump to content
MikeMon

Using Expressions in the Group By Clause in Interbase

Recommended Posts

Hi

 

In Interbase, does anybody know how to use an expression in the "group by" clause, e.g.:


select extract (month from HIRED_DATE) HIREDMONTH, count(*) EMPLOYEECOUNT from employee group by extract (month from HIRED_DATE)

 

or

 

select extract (month from HIRED_DATE) HIREDMONTH, count(*) EMPLOYEECOUNT from employee group by 1

Share this post


Link to post

I've not used Interbase, but if follows std SQL, it could be something like this?

 

SELECT HIREDMONTH, EMPLOYEECOUNT FROM   (
	SELECT extract (month FROM HIRED_DATE) AS HIREDMONTH, count(*) AS EMPLOYEECOUNT 
	FROM employee
	) AS Table1
GROUP BY HIREDMONTH, EMPLOYEECOUNT 

 

Share this post


Link to post
44 minutes ago, Lars Fosdal said:

I've not used Interbase, but if follows std SQL, it could be something like this?

 


SELECT HIREDMONTH, EMPLOYEECOUNT FROM   (
	SELECT extract (month FROM HIRED_DATE) AS HIREDMONTH, count(*) AS EMPLOYEECOUNT 
	FROM employee
	) AS Table1
GROUP BY HIREDMONTH, EMPLOYEECOUNT 

 

Hi Lars

 

Thank you for your answer again. Unfortunately, the derived table feature (nested select) was introduced in Interbase 2017. I'm still using Interbase XE3. Actually, after doing some research, expressions in the group by clause I mentioned above were also introduced in Interbase 2017.

Share this post


Link to post
1 hour ago, Lars Fosdal said:

An alternative could be using a temp table for the inner select?

Hi Lars

 

Not possible. As a workaround, I created computed (calculated) fields and used them in the group by clause. 

  • Like 1

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×