Sql-server – SQL Trying to sum and group by distinct values

distinctgroup bysql serversum

I have a table

+-------+----------+------------+----------+---------+
| 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 |
+-------+----------+------------+----------+---------+

I am trying to count the number of different materials, and sum the quantities along the LineName, so that I get something like this:

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

Instead, when I use a combination of count distinct, sum and group by, I get an incorrect result:

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

How should I do this correctly? I've tried with

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

But it does not create the desired result. I've been looking on stack exchange for some similar topics, but they don't seem to match my question.

Best Answer

Following confirmation in the comments it seems that you need this

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
Related Question