SQL错误:ORA-00979:不是GROUP BY表达式

Question

显示订货总数量大于等于100的订货物料的物料编码、供应商名称、总数量。

SELECT QUOT.sname, ORD.Qtyordered,SUM(ORD.QTYORDERED)
from Quotation QUOT
    inner join Orders ORD ON QUOT.QuotationId = ORD.QuotationId
GROUP BY QUOT.sname 
HAVING SUM(ORD.QTYORDERED) >=100 /* No Error */

SELECT QUOT.Itemcode, QUOT.sname, ORD.Qtyordered, SUM(ORD.QTYORDERED)
from Quotation QUOT
    inner join Orders ORD ON QUOT.QuotationId = ORD.QuotationId
GROUP BY QUOT.sname 
HAVING SUM(ORD.QTYORDERED) >=100 /* ERROR not a group by expression */

我是SQL的初学者，有人能帮我写一个查询吗？

Answer 1

不要选择ORD.Qtyordered或ITEM_CODE作为单独的列。它们不适用于sname的聚合查询

SELECT QUOT.sname, SUM(ORD.QTYORDERED)
from Quotation QUOT inner join
     Orders ORD
     ON QUOT.QuotationId = ORD.QuotationId
GROUP BY QUOT.sname 
HAVING SUM(ORD.QTYORDERED) >= 100 ;

如果你想要这些项目，你可能需要LISTAGG()。

或者，对于提出的问题，将该项目包括在GROUP BY中

SELECT QUOT.Itemcode, QUOT.sname, SUM(ORD.QTYORDERED)
from Quotation QUOT inner join
     Orders ORD
     ON QUOT.QuotationId = ORD.QuotationId
GROUP BY QUOT.Itemcode, QUOT.sname 
HAVING SUM(ORD.QTYORDERED) >= 100 ;

100的限制是订单中的每个项目，而不是项目。

Answer 2

看起来您只需要调整查询的GROUP BY来满足您拥有的其他列的需求：

SELECT  QUOT.Itemcode,
        QUOT.sname,
        SUM(ORD.QTYORDERED)
  FROM  Quotation QUOT
    INNER JOIN Orders ORD ON QUOT.QuotationId = ORD.QuotationId
  GROUP BY QUOT.Itemcode, QUOT.sname 
  HAVING SUM(ORD.QTYORDERED) >=100

在使用GROUP BY执行聚合操作时，每个非聚合列都应该包含在GROUP BY语句中。