Cannot use expressions in GROUP BY clause

[monetdb-team]

Date: 2015-10-22 09:13:12 +0200
From: Sherzod Mutalov <>
To: SQL devs <>
Version: 11.21.5 (Jul2015)
CC: g.ozolins, jspeis, martin.van.dinther, @PedroTadim, @yzchang

Last updated: 2019-04-30 12:36:03 +0200

Comment 21378

Date: 2015-10-22 09:13:12 +0200
From: Sherzod Mutalov <>

User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:39.0) Gecko/20100101 Firefox/39.0
Build Identifier:

Cannot use SQL expressions with GROUP BY clause, which causes error. Similar query works on SQL Server, PostgreSQL and MySQL

Reproducible: Always

Steps to Reproduce:

Execute next statements:

1. CREATE TABLE test("State" string, "Sales" double);
2. INSERT INTO test VALUES('Texas', 125);

SELECT LEFT("State", 3) AS "State",
SUM("Sales") AS "Sales"
FROM test
GROUP BY LEFT("State", 3);

Actual Results:

syntax error, unexpected LEFT in: "select left("State", 3) as "State",
sum("Sales") as "Sales"
from test
group by l"

Expected Results:

+-------+--------------------------+
| State | Sales |
+=======+==========================+
| Tex | 450 |
+-------+--------------------------+

MonetDB version:
MonetDB 5 server v11.21.5 "Jul2015"
Serving database 'demo', using 4 threads
Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked
Found 15.916 GiB available main-memory.
Copyright (c) 1993-July 2008 CWI.
Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved
Visit http://www.monetdb.org/ for further information
Listening for connection requests on mapi:monetdb://127.0.0.1:50000/
MonetDB/GIS module loaded
Start processing logs sql/sql_logs version 52200
Start reading the write-ahead log 'sql_logs\sql\log.12'
Finished reading the write-ahead log 'sql_logs\sql\log.12'
Finished processing logs sql/sql_logs
MonetDB/SQL module loaded

Comment 21379

Date: 2015-10-22 09:16:20 +0200
From: Sherzod Mutalov <>

Read steps to reproduce as below:

Execute next statements:

1. CREATE TABLE test("State" string, "Sales" double);
2. INSERT INTO test VALUES('Texus', 200);
3. INSERT INTO test VALUES('Texas', 250);

SELECT LEFT("State", 3) AS "State",
SUM("Sales") AS "Sales"
FROM test
GROUP BY LEFT("State", 3);

Comment 21380

Date: 2015-10-22 11:56:42 +0200
From: @yzchang

This is not a bug, but a missing feature. A.f.a.I.k., MonetDB doesn't support expressions in GROUP BY yet. It only support references to columns. Changed this report into "enhancement".

Comment 21381

Date: 2015-10-22 13:03:42 +0200
From: Sherzod Mutalov <>

What a pity, so I have to pervert queries. Hopefully this feature will be added in the next release.

Comment 21462

Date: 2015-11-05 12:53:49 +0100
From: Martin van Dinther <<martin.van.dinther>>

There are some workarounds for this:

-- use a subquery in the FROM-clause:
SELECT "State3", SUM("Sales") AS "Sales"
FROM (SELECT LEFT("State", 3) AS "State3", "Sales" FROM test) AS test
GROUP BY "State3";

-- use a view to extend the table with the expression colum(s) once
CREATE VIEW test_vw AS
SELECT LEFT("State", 3) AS "State3", "State", "Sales" FROM test;
-- and next query the view instead of the table
SELECT "State3", SUM("Sales") AS "Sales"
FROM test_vw
GROUP BY "State3";

Hope this helps.

Comment 22035

Date: 2016-04-14 12:11:36 +0200
From: Martin van Dinther <<martin.van.dinther>>

You can also use the column alias in the GROUP BY (and also in HAVING).
So a simpler workaround is:
SELECT LEFT("State", 3) AS "StateFirst3chars", SUM("Sales") AS "Sales"
FROM test
GROUP BY "StateFirst3chars";

Comment 22191

Date: 2016-06-02 15:28:03 +0200
From: Gatis Ozolins <<g.ozolins>>

This is also issue for us, where BI tools are generating SQL

Comment 25081

Date: 2017-03-02 16:15:26 +0100
From: MonetDB Mercurial Repository <>

Changeset 3d3a3776b749 made by Martin van Dinther martin.van.dinther@monetdbsolutions.com in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=3d3a3776b749

Changeset description:

Adding missing test script for old bug #3832.
It would be nice if support for groupby_on_column_expressions could be implemented.

Comment 26404

Date: 2018-04-23 22:03:11 +0200
From: jspeis

would be great to see this feature added!

Comment 26741

Date: 2018-12-20 16:35:53 +0100
From: @PedroTadim

This feature will be available in the next feature release.