sqldatabasepostgresql

SQL aggregate function with alias for aggregate function. Why total seats gives an error and just using seats doesn't?

Why does this query

SELECT id, SUM(seats) AS Total seats
FROM  bookings
GROUP BY id
ORDER BY Total seats;

throw an error:

ERROR: syntax error at or near "seats"

But this query doesn't:

SELECT id, SUM(seats) AS seats
FROM  bookings
GROUP BY id
ORDER BY seats;

Maybe something very trivial but I don't have any idea.

Solution

  • Because multi-word aliases are written differently.

    SQL identifiers and key words must begin with a letter (a-z, but also letters with diacritical marks and non-Latin letters) or an underscore (_). Subsequent characters in an identifier or key word can be letters, underscores, digits (0-9), or dollar signs ($). Note that dollar signs are not allowed in identifiers according to the letter of the SQL standard, so their use might render applications less portable. The SQL standard will not define a key word that contains digits or starts or ends with an underscore, so identifiers of this form are safe against possible conflict with future extensions of the standard.

    Identifiers with spaces need to be double-quoted:

    Quoted identifiers can contain any character, except the character with code zero. (To include a double quote, write two double quotes.) This allows constructing table or column names that would otherwise not be possible, such as ones containing spaces or ampersands.

    Please have a look:

    select now() as "current date";--no error
select now() as current date;--error
select now() as current_date;--no error