时间戳

首页 > 解决方案 > 聚合函数错误。下面列出的详细信息

python - 聚合函数错误。下面列出的详细信息

问题描述

我正在编写一个 SQL 查询以从 northwind.orders 创建一个名为 orders_years 的新表，显示 1996 年、1997 年和 1998 年每个（不同）客户下的订单数量。我还按 customer_id 对结果进行分组并排序它们由customer_id，在运行下面的代码时，它给了我一个错误，说FROM LINE 2中的函数中不允许使用聚合函数：SUM

-- 创建新表orders_years：

SELECT customer_id FROM northwind.orders,
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;
SELECT orders_years FROM northwind.orders
GROUP BY customer_id
ORDER BY customer_id;

标签： pythonmysqlsqlpostgresql

解决方案

from 子句在错误的位置

CREATE TABLE orders (customer_id int, order_date date)

INSERT INTO orders VALUES (1, '1997-01-01'), (2, '1998-01-01')

CREATE tABLE orders_years
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 orders
GROUP BY customer_id
ORDER BY customer_id;;
    

SELECT * FROM orders_years

客户 ID | 订单_96 | 订单_97 | 订单_98
----------: | --------: | --------: | --------:
          1 | 0 | 1 | 0
          2 | 0 | 0 | 1

db<>在这里摆弄

推荐阅读