Using Expressions in the Group By Clause in Interbase

[MikeMon 12]

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

[Lars Fosdal 1792]

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 

 

[MikeMon 12]

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.

[Lars Fosdal 1792]

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

[Fr0sT.Brutal 900]

I can't believe it took a decade for them to implement SELECT FROM SELECT

[MikeMon 12]

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. 

[Joseph MItzen 251]

On 12/2/2019 at 2:32 AM, Fr0sT.Brutal said:

I can't believe it took a decade for them to implement SELECT FROM SELECT

Quote

"I could easily fill a book with good reasons why InterBase rocks. Limiting the coverage to a modest five was challenging. InterBase represents a rich legacy of excellence that spans decades combined with continued innovation that never stops moving it forward. I find myself wanting to add more and more, just to get the message across."

-John Aasenden, blogging for Embarcadero

 

They added Common Table Expressions 8 years after PostgreSQL (although they still don't have the recursive option, which PostgreSQL has had for 11 years now), tablespaces 15 years after PostgreSQL... they still don't support partial indexes, functional indexes, window functions, UPSERT, full text search or even timestamp with timezone. No compression support, materialized views, partition support... Their marketing material still brags about being "SQL-92 compliant". It's only been in the last few years that Embarcadero has made any user-facing improvements at all to Interbase.

I remember a blog post by MVP Warren Postma several years ago about the Interbase Developer Edition shutting down after four hours (I think it's up to 48 hours now) and how ridiculous that is given that SQL Server, Oracle, etc. give free developer editions without the forced shutdowns. David Intersimone appeared in the comments and said that four hours was plenty of time to test any code and they didn't want people stealing the database. Warren, MVP status be damned, asked David why anyone would go through all the trouble of stealing Interbase when there were much better databases they could have for free? David didn't respond to that.

 

Even SQLite offers window functions, partial indexes, functional indexes, JSON support, full text search, UPSERT and common table expressions with recursion now. (It's actually a damn fine database for performing data analysis on the desktop now; I'd choose it for business intelligence or analytics work over Interbase in a heartbeat).

 

I leave you with four more quotes to ponder....

 

Quote

Since 1999, SQL is not limited to the relational model anymore. Back then [the SQL standard] added arrays, objects and recursive queries. In the meanwhile the SQL standard has grown five times bigger than SQL-92. In other words: relational SQL is only about 20% of modern SQL.

-Markus Winand

Quote

A revised version of the SQL standard is released from time to time; the most recent update appearing in 2016.... Each version replaces the previous one, so claims of conformance to earlier versions have no official merit....

SQL-92 defined three feature sets for conformance: Entry, Intermediate, and Full. Most database management systems claiming SQL standard conformance were conforming at only the Entry level, since the entire set of features in the Intermediate and Full levels was either too voluminous or in conflict with legacy behaviors.

Starting with SQL:1999, the SQL standard defines a large set of individual features rather than the ineffectively broad three levels found in SQL-92. A large subset of these features represents the “Core” features, which every conforming SQL implementation must supply. The rest of the features are purely optional.

...PostgreSQL supports most of the major features of SQL:2016. Out of 177 mandatory features required for full Core conformance, PostgreSQL conforms to at least 170. In addition, there is a long list of supported optional features. It might be worth noting that at the time of writing, no current version of any database management system claims full conformance to Core SQL:2016.

-PostgreSQL 13 documentation

 

Quote

 

For business data on mobile, InterBase ToGo is packed with superior disaster recovery and data-protection compliance features that are key to maintaining both your and your customers’ reputations. With protection throughout the development life cycle, InterBase massively reduces the risk of fines and regulatory action in the event of lost data.

SQLite is basically a flat file on steroids; SQLite does [sic?] offer the same level of database support to ensure data integrity and doesn’t for high concurrency. Because of this, it is not really suited for ISV development.

-Embarcadero

 

Quote

 

SQLite is likely used more than all other database engines combined. Billions and billions of copies of SQLite exist in the wild. SQLite is found in:

Every Android device

Every iPhone and iOS device

Every Mac

Every Windows10 machine

Every Firefox, Chrome, and Safari web browser

Every instance of Skype

Every instance of iTunes

Every Dropbox client

Every TurboTax and QuickBooks

PHP and Python

Most television sets and set-top cable boxes

Most automotive multimedia systems

Countless millions of other applications

Since SQLite is used extensively in every smartphone, and there are more than 4.0 billion (4.0e9) smartphones in active use, each holding hundreds of SQLite database files, it is seems likely that there are over one trillion (1e12) SQLite databases in active use.

-SQLite.org

 

 

 

Edited September 28, 2021 by Joseph MItzen

[Stano 143]

I was very surprised by the data on SQLite 

[Guest]

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

 

Should work (at least in FB). What error(s) do you get?

[Fr0sT.Brutal 900]

@Joseph MItzen

I've never used IB since v6 I believe (the one shipped with D7), I switched to FB and never looked back