Doubts on Group By with ALias

Question

SELECT
    first_name || ' ' || last_name full_name,
    SUM (amount) amount
FROM
    payment
INNER JOIN customer USING (customer_id)     
GROUP BY
    full_name
ORDER BY amount;

Can i know how does this query works for group by full_name, by rigth the query should give error because of the order of the sql execution(https://stackoverflow.com/a/3841804/15279872). The output of the above query can be seen in this link (https://www.postgresqltutorial.com/postgresql-group-by/) under the Section 3 : Using PostgreSQL GROUP BY clause with the JOIN clause

But how can the query gives the output without showing any error at the group by clause — macDaemon, Mar 05 '21 at 03:27

score 2 · Answer 1 · edited Mar 05 '21 at 07:27

Here is what the documentation says about GROUP BY in PostgreSQL:

In the SQL-92 standard, an ORDER BY clause can only use output column names or numbers, while a GROUP BY clause can only use expressions based on input column names. PostgreSQL extends each of these clauses to allow the other choice as well (but it uses the standard's interpretation if there is ambiguity). PostgreSQL also allows both clauses to specify arbitrary expressions. Note that names appearing in an expression will always be taken as input-column names, not as output-column names.

SQL:1999 and later use a slightly different definition which is not entirely upward compatible with SQL-92. In most cases, however, PostgreSQL will interpret an ORDER BY or GROUP BY expression the same way SQL:1999 does.

The “output column name” mentioned above can be an alias, so PostgreSQL allows aliases in the GROUP BY clause.

Doubts on Group By with ALias

1 Answers1