SQL嘗試按不同的值求和和分組

我有一張桌子

+-------+----------+------------+----------+---------+
| Plant | LineName | WorkCenter | Material | ProdQty |
+-------+----------+------------+----------+---------+
| x     | xl       | xl1        | y1       |       1 |
| x     | xl       | xl2        | y1       |       1 |
| x     | xl       | xl3        | y1       |       1 |
| x     | xl       | xl1        | y2       |       1 |
| x     | xl       | xl2        | y2       |       1 |
| x     | xl       | xl3        | y2       |       1 |
+-------+----------+------------+----------+---------+

我正在嘗試計算不同材料的數量，並將沿 LineName 的數量求和，以便得到如下資訊：

+----------+------+---------+
| LineName | Cmat | ProdQty |
+----------+------+---------+
| xl       |    2 |       2 |
+----------+------+---------+

相反，當我使用 count distinct、sum 和 group by 的組合時，我得到一個不正確的結果：

+----------+------+---------+
| LineName | Cmat | ProdQty |
+----------+------+---------+
| xl       |    2 |       6 |
+----------+------+---------+

我應該如何正確地做到這一點？我試過了

SELECT LineName, COUNT(DISTINCT(Material) as Cmat, SUM(ProdQty) as ProdQty
FROM table
GROUP BY LineName

但它不會產生預期的結果。我一直在尋找一些類似主題的堆棧交換，但它們似乎與我的問題不符。

在評論中確認後，您似乎需要這個

WITH Dist
    AS (SELECT DISTINCT LineName,
                        Material,
                        ProdQty
        FROM   [table])
SELECT LineName,
      COUNT(Material) AS Cmat,
      SUM(ProdQty)    AS ProdQty
FROM   Dist
GROUP  BY LineName 

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