SQL创建orders_years表报聚合函数不允许在FROM子句中错误

错误原因 #

• 语法顺序错误：误将FROM northwind.orders写在了聚合字段的前面，还加了逗号分隔，导致数据库把SUM聚合函数判定为FROM子句中的内容，触发对应报错
• 语句拆分错误：错误使用分号将字段查询、分组排序逻辑拆成了两个独立查询，逻辑完全不连贯
• 缺少建表语法：没有加CREATE TABLE ... AS语句，无法将查询结果存储为新的orders_years表

修复后可运行代码 #

兼容绝大多数支持标准SQL的数据库：

-- 创建新表orders_years
CREATE TABLE orders_years AS
SELECT 
    customer_id,
    SUM(CASE WHEN EXTRACT(YEAR FROM order_date) = 1996 THEN 1 ELSE 0 END) AS orders_96,
    SUM(CASE WHEN EXTRACT(YEAR FROM order_date) = 1997 THEN 1 ELSE 0 END) AS orders_97,
    SUM(CASE WHEN EXTRACT(YEAR FROM order_date) = 1998 THEN 1 ELSE 0 END) AS orders_98
FROM northwind.orders
GROUP BY customer_id
ORDER BY customer_id;

如果使用PostgreSQL等支持FILTER语法的数据库，可使用更简洁的写法：

CREATE TABLE orders_years AS
SELECT 
    customer_id,
    COUNT(*) FILTER (WHERE EXTRACT(YEAR FROM order_date) = 1996) AS orders_96,
    COUNT(*) FILTER (WHERE EXTRACT(YEAR FROM order_date) = 1997) AS orders_97,
    COUNT(*) FILTER (WHERE EXTRACT(YEAR FROM order_date) = 1998) AS orders_98
FROM northwind.orders
GROUP BY customer_id
ORDER BY customer_id;

内容的提问来源于stack exchange，提问作者Biruk