AWS Athena ALIAS in Group By does not get resolved

Question

I have a very basic group by query in Athena where I would like to use an alias. One can make the example work by putting the same reference in the group by, but that's not really handy when there's complex column modifications going on and logic needs to be copied in two places. Also I did that in the past and now I have a statement that doesn't work by copying over.

Problem:

SELECT 
    substr(accountDescriptor, 5) as account, 
    sum(revenue) as grossRevenue 
FROM sales 
GROUP BY account

This will throw an error:

alias Column 'account' cannot be resolved

The following works, so it's about the alias handling.

SELECT 
    substr(accountDescriptor, 5) as account, 
    sum(revenue) as grossRevenue 
FROM sales 
GROUP BY substr(accountDescriptor, 5)

Hello Supernova, Hello, you cant group by alias. That's it. – zip Feb 10 '20 at 00:52 — zip, Feb 10 '20 at 00:52

kokosing · Accepted Answer · 2020-02-10T12:14:20.443

That is because SQL is evaluated in certain order, like table scan, filter, aggregation, projection, sort. You tried to use the result of projection as input of aggregation. In many cases it could be possible (where projection is trivial, like your case), but it such behaviour is not defined in ANSI SQL (which Presto and so Athena follows).

We see that in many cases it is very useful so, support for this might be added in future (extending ANSI SQL).

Currently, there are several ways to overcome this:

SELECT account, sum(revenue) as grossRevenue 
FROM (SELECT substr(accountDescriptor, 5) as account, revenue FROM sales)
GROUP BY account

or

WITH better_sales AS (SELECT substr(accountDescriptor, 5) as account, revenue FROM sales)
SELECT account, sum(revenue) as grossRevenue 
FROM better_sales
GROUP BY account

or

SELECT account, sum(revenue) as grossRevenue 
FROM sales
LATERAL JOIN (SELECT substr(accountDescriptor, 5) as account)
GROUP BY account

or

SELECT substr(accountDescriptor, 5) as account, sum(revenue) as grossRevenue
FROM sales
GROUP BY 1;

Ilya Kisil · Answer 2 · 2020-06-13T11:04:51.760

In addition to answers from kokosing and Gordon Linoff, you can use numbers that represent the location of the grouped column name in the SELECT statement. Such approach can also provide you with better performance as described in section 8 of this AWS Blog. For example:

SELECT
    substr(accountDescriptor, 5) as account,
    sum(revenue) as grossRevenue
FROM sales,
GROUP BY 1

Note: numbering starts from one and not from zero.

Here 1 is somewhat aliased to account. The main obvious downside is that if you change ordering of you columns within SELECT than you would also need to account for that within GROUP BY:

SELECT
    sum(revenue) as grossRevenue,
    substr(accountDescriptor, 5) as account
FROM sales,
GROUP BY 2

Gordon Linoff · Answer 3 · 2020-02-10T01:47:11.903

0

Hive does not allow column aliases in the GROUP BY -- just as the SQL standard does not allow them. Some databases extend SQL to allow aliases, but this is an extension.

Just repeat the expression:

SELECT substr(accountDescriptor, 5) as account, sum(revenue) as grossRevenue
FROM sales
GROUP BY substr(accountDescriptor, 5);

edited Feb 10 '20 at 01:47

answered Feb 10 '20 at 00:53

Gordon Linoff

1,242,037
58
646
786

Ok, so there is no solution for this. Does anybody know the reason why this cannot be done in the query parser? Would be interested in the internal mechanics rather than a "does not work". The working solution has been included in the question by the way. – supernova Feb 10 '20 at 01:40
@supernova . . . SQL is defined by a standard and the standard does not allow aliases in the `GROUP BY`. Some databases extend SQL to allow them. – Gordon Linoff Feb 10 '20 at 01:47

AWS Athena ALIAS in Group By does not get resolved

3 Answers3