Oracle计算总金额遇‘Not a single group function’错误求助

问题背景 #

取消注释查询中计算总金额的sum(tot_quantity * i.PRICE)语句时，触发Not a single group function错误，无法完成查询。以下是完整的Oracle测试环境及出错的查询代码：

测试环境脚本 #

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY  HH24:MI:SS.FF';

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

CREATE TABLE customers 
(CUSTOMER_ID, FIRST_NAME, LAST_NAME) AS
SELECT 1, 'Faith', 'Aaron' FROM DUAL UNION ALL
SELECT 2, 'Lisa', 'Saladino' FROM DUAL UNION ALL
SELECT 3, 'Bonnie', 'Winterbottom' FROM DUAL UNION ALL
SELECT 4, 'Beth', 'Zanzone' FROM DUAL UNION ALL
SELECT 5, 'Sandy', 'Herring' FROM DUAL;

ALTER TABLE customers 
ADD CONSTRAINT customers_pk PRIMARY KEY (customer_id);

CREATE TABLE items 
(PRODUCT_ID, PRODUCT_NAME, PRICE) AS
SELECT 100, 'Presto 6-quart Pressure Cooker', 79.99 FROM DUAL UNION ALL
SELECT 101, 'Cuisinart 8-quart Pressure Cooker', 111.99 FROM DUAL UNION ALL
SELECT 102, 'Farberware 3-quart Pressure Cooker', 49.99 FROM DUAL UNION ALL
SELECT 103, 'Farberware 6-quart Pressure Cooker', 89.29 FROM DUAL UNION ALL
SELECT 104, 'Farberware 8-quart Pressure Cooker', 105.99 FROM DUAL;

ALTER TABLE items 
ADD CONSTRAINT items_pk PRIMARY KEY (product_id);

create table purchases(
  ORDER_ID NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
  customer_id   number, 
  PRODUCT_ID NUMBER, 
  QUANTITY NUMBER, 
  purchase_date timestamp
);

ALTER TABLE purchases 
ADD CONSTRAINT order_pk PRIMARY KEY (order_id);

ALTER TABLE purchases ADD CONSTRAINT customers_fk FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

ALTER TABLE purchases ADD CONSTRAINT items_fk FOREIGN KEY (PRODUCT_ID) REFERENCES items(product_id);

insert  into purchases (customer_id, product_id, quantity, purchase_date) 
SELECT 3, 102, 4,TIMESTAMP '2022-12-22 21:44:35' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM    dual
CONNECT BY  LEVEL <= 15 UNION ALL 
select 1, 101,3, date '2023-03-29' + level * interval '2' day from dual
          connect by level <= 12
union all
select 2, 101,2, date '2023-01-15' + level * interval '8' hour from dual
          connect by level <= 15
union all
select 2, 102,2,date '2023-04-13' + level * interval '1 1' day to hour from dual
          connect by level <= 11
union all
select 3, 101,2, date '2023-02-01' + level * interval '1 05:03' day to minute from dual
          connect by level <= 10
union all
select 3, 101,1, date '2023-04-22' + level * interval '23' hour from dual
          connect by level <= 23
union all
select 3, 100,1,  date '2022-03-01' + level * interval '1 00:23:05' day to second from dual
          connect by level <= 15
union all
select 3, 101,1,  date '2022-03-01' + level * interval '1 00:23:05' day to second from dual
          connect by level <= 15
union all
select 4, 102,1, date '2023-01-01' + level * interval '5' hour from dual
          connect by level <= 60;

出错的查询代码 #

/* Total quantity purchased each item */

with prep (product_id, tot_quantity) as 
(
   select 
      product_id, 
      sum(quantity)
    from   purchases
    group  by product_id
  )
SELECT  
     p.product_id, 
     I.product_name,
     tot_quantity,
/*
     sum(tot_quantity * i.PRICE) 
"TOTAL_AMT"
*/
 FROM prep p
JOIN items i ON i.product_id = p.product_id  
ORDER BY tot_quantity desc,
product_id;

错误原因 #

Oracle分组函数规则明确：若SELECT子句同时包含聚合函数（如sum()）和非聚合列，所有非聚合列必须出现在GROUP BY子句中。

当前查询中，sum(tot_quantity * i.PRICE)是聚合函数，但同时查询了p.product_id、I.product_name、tot_quantity这些非聚合列且未添加GROUP BY，数据库无法确定分组逻辑，因此触发错误。

另外需注意：prep子查询已按product_id分组算出单商品总购买量tot_quantity，单商品总金额仅需tot_quantity * i.PRICE即可计算，无需再用sum()——误用sum()会将所有商品总金额累加，通常不符合需求。

修复方案 #

方案1：计算单商品总金额（无需聚合） #

若需求是每个商品的总购买金额，直接移除sum()即可，因为prep已按商品分组，每组仅一条记录：

with prep (product_id, tot_quantity) as 
(
   select 
      product_id, 
      sum(quantity)
    from   purchases
    group  by product_id
  )
SELECT  
     p.product_id, 
     I.product_name,
     tot_quantity,
     tot_quantity * i.PRICE AS "TOTAL_AMT"
 FROM prep p
JOIN items i ON i.product_id = p.product_id  
ORDER BY tot_quantity desc,
product_id;

方案2：计算所有商品总金额合计（需聚合） #

若需求是所有商品的总金额总和，可通过窗口函数或子查询实现：

with prep (product_id, tot_quantity) as 
(
   select 
      product_id, 
      sum(quantity)
    from   purchases
    group  by product_id
  )
SELECT  
     p.product_id, 
     I.product_name,
     tot_quantity,
     sum(tot_quantity * i.PRICE) OVER() AS "TOTAL_AMT" -- 窗口函数计算全局总和
 FROM prep p
JOIN items i ON i.product_id = p.product_id  
ORDER BY tot_quantity desc,
product_id;

或使用子查询：

with prep (product_id, tot_quantity) as 
(
   select 
      product_id, 
      sum(quantity)
    from   purchases
    group  by product_id
  )
SELECT  
     p.product_id, 
     I.product_name,
     tot_quantity,
     (SELECT sum(tot_quantity * PRICE) FROM prep p2 JOIN items i2 ON p2.product_id = i2.product_id) AS "TOTAL_AMT"
 FROM prep p
JOIN items i ON i.product_id = p.product_id  
ORDER BY tot_quantity desc,
product_id;

验证结果 #

执行修复后的查询，将得到每个商品的购买量及对应总金额（或全局总金额），不再触发Not a single group function错误。

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