GROUP BY 子句/聚合函式錯誤

我在創建表視圖時遇到問題：

DROP VIEW IF EXISTS product_variation_stock_view;

CREATE VIEW product_variation_stock_view AS
   SELECT
       product_variations.product_id AS product_id,
       product_variations.id AS product_variation_id,
       COALESCE(SUM(stocks.quantity) - COALESCE(SUM(product_variation_order.quantity), 0), 0) AS stock,
       CASE WHEN COALESCE(SUM(stocks.quantity) - COALESCE(SUM(product_variation_order.quantity), 0), 0) > 0
           THEN true
           ELSE false
       END in_stock
   FROM product_variations

   LEFT JOIN (
       SELECT 
           stocks.product_variation_id AS id,
           SUM(stocks.quantity) AS quantity
       FROM stocks
       GROUP BY 
           stocks.product_variation_id
   ) AS stocks USING (id)

   LEFT JOIN (
       SELECT
           product_variation_order.product_variation_id as id, 
           SUM(product_variation_order.quantity) AS quantity
       FROM product_variation_order
       GROUP BY 
           product_variation_order.product_variation_id
   ) AS product_variation_order USING (id)

錯誤：

ERROR:  column "product_variations.product_id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 3:   product_variations.product_id AS product_id,
     ^

通過 Laravel Homestead 在 Ubuntu 18.04 上使用 PostgreSQL 10.3 我正在使用 Postico OS X 客戶端執行查詢。

看起來您可能必須更改前幾行以反映這一點：

CREATE VIEW product_variation_stock_view AS
SELECT
   product_variations.product_id AS product_id,
   product_variations.id AS product_variation_id,
   COALESCE(SUM(stocks.quantity) - COALESCE(SUM(product_variation_order.quantity), 0), 0) AS stock,
   CASE WHEN COALESCE(SUM(stocks.quantity) - COALESCE(SUM(product_variation_order.quantity), 0), 0) > 0
       THEN true
       ELSE false
   END in_stock
GROUP BY product_variations.id, product_variations.product_id
FROM product_variations

由於錯誤消息表明您將需要一個分組：

SELECT  product_variations.product_id AS product_id,
       product_variations.id AS product_variation_id,
       COALESCE(SUM(stocks.quantity) ...
FROM product_variations
...
GROUP BY product_variations.product_id, product_variations.id  

因為你在最外層有聚合函式

引用自：https://dba.stackexchange.com/questions/217422