SQL |如何在同一個表中求和但引用不同的單元格值

例如，我有一張這樣的表：

CREATE TABLE Sample
   (`id` int, `productName` varchar(7), `description` varchar(55), `quantity`int(10))
;

INSERT INTO Sample
   (`id`, `productName`, `description`, `quantity`)
VALUES
   (1, 'Cheese', 'GT', 10),
   (1, 'Cheese', 'GE', 10),
   (1, 'Cheese', 'GE', 10),
   (2, 'Ham', 'VD', 10),
   (3, 'Wine', 'EX', 5)
;

並且我試圖得到一個結果，以便如果描述是 valueGT和GEor ，則計算會有所不同VD。我試過了union all，但它似乎不起作用：

SELECT x.productName, SUM(x.quantity)
 FROM ( SELECT productName, SUM(quantity*10)
       FROM Sample
       WHERE description IN ('GT', 'GE')
       UNION ALL
       SELECT productName, SUM(quantity*20)
       FROM Sample
       WHERE description IN ('VD')
      ) AS x

這可能嗎？

• GROUP BY使用聚合函式時不要忘記子句。
• 您的內部 SUM 沒有別名，因此x.quantity不起作用。

嘗試這個 ：

SELECT x.productName, SUM(x.quantity) as quantity
FROM 
(
   SELECT productName, SUM(quantity*10) as quantity
   FROM Sample
   WHERE description IN ('GT','GE')
   GROUP BY productName

   UNION ALL
   SELECT productName, SUM(quantity*20) as quantity
   FROM Sample
   WHERE description IN ('VD') 
   GROUP BY productName
) AS x
GROUP BY x.productName

with GT_GE
   as
   (
   select productname, sum(quantity*10) Quantity
   from sample
   where description IN ('GT','GE')
   group by productname
   )
   ,
   VD as
   (
   select productname, sum(quantity*20) Quantity
   from sample
   where description = 'VD'
   group by productname
   )
   select GT_GE.productname, GT_GE.Quantity, VD.productname, VD.quantity 

   from GT_GE, VD

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