PostgreSQLでは副問い合わせ(subquery)に別名を付けないと怒られたメモ

怒られたSQL文

SELECT
    年月,
    SUM(販売金額) AS 販売合計金額
FROM
    (
        SELECT
            TO_CHAR(s.sale_date, 'yyyy-mm') AS 年月,
            s.quantity * p.price AS 販売金額
        FROM
            sales AS s
                JOIN
            products AS p ON  s.product_id = p.product_id
    )
GROUP BY
    年月
ORDER BY
    年月
;

----------------------------------------------------
db_name=# 上記SQL文
ERROR:  subquery in FROM must have an alias
LINE 1: select 年月, sum(販売金額) as 販売合計金額 from (select TO_C...
                                                        ^
HINT:  For example, FROM (SELECT ...) [AS] foo.

修正したSQL文

SELECT
    年月,
    SUM(販売金額) AS 販売合計金額
FROM
    (
        SELECT
            TO_CHAR(s.sale_date, 'yyyy-mm') AS 年月,
            s.quantity * p.price AS 販売金額
        FROM
            sales AS s
                JOIN
            products AS p ON  s.product_id = p.product_id
    ) AS 販売実績別合計金額 !!!!ココ!!!!
GROUP BY
    年月
ORDER BY
    年月
;

補足：そもそも副問い合わせを使わないSQL文

SELECT
    TO_CHAR(s.sale_date, 'yyyy-mm') AS 年月,
    SUM(s.quantity * p.price) AS 合計販売金額
FROM
    sales AS s
        JOIN
    products AS p ON  s.product_id = p.product_id
GROUP BY
    TO_CHAR(s.sale_date, 'yyyy-mm')
ORDER BY
    TO_CHAR(s.sale_date, 'yyyy-mm')
;