# SQL Trying to sum and group by distinct values

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.

• Please explain the logic as to why `ProdQty` should equal `2` Is this what you are trying to do? `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` May 21, 2020 at 15:38
• Thanks! That was it! Well... The logic is that LineName represents a production line. So if you look at how many different material quantities line xl has produced, it is in fact only 2.
– LRO
May 21, 2020 at 16:46
• What should the result be if we had 3 rows, with quantities 1, 1 and 4? The result should be? 5 or 6? May 21, 2020 at 23:30
• It depends on the format in the above table, Material, WorkCenter etc. Luckily, the ugly table is kinda fixed in nature. Hence, 1,1,4 would never come up on the same material on different work centres across the same lines.
– LRO
May 22, 2020 at 11:20

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
``````
• Despite OP's claim that this the wanted result, the query and the wanted results look completely illogical. May 21, 2020 at 20:17
• @ypercubeᵀᴹ illogical if their schema is correctly normalised. From their desired results I assume it isn't though. Hopefully they will come back and clarify if this isn't what they actually needed May 21, 2020 at 20:24
• Yeah, +1 to the answer. My comment was for OP, not you. May 21, 2020 at 23:27
• It is correct that it isn't normalised. It is intentionally, as it is taken from a DW, moved into PowerBI, and then back into a DB format. I do not have access to the source data, which obviously would have made the task easier because of normalised data. I just needed a quick fix for my table.
– LRO
May 22, 2020 at 11:14