1

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.

  • 2
    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 – Martin Smith May 21 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 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? – ypercubeᵀᴹ May 21 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 at 11:20
2

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 
| improve this answer | |
  • Despite OP's claim that this the wanted result, the query and the wanted results look completely illogical. – ypercubeᵀᴹ May 21 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 – Martin Smith May 21 at 20:24
  • 1
    Yeah, +1 to the answer. My comment was for OP, not you. – ypercubeᵀᴹ May 21 at 23:27
  • 1
    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 at 11:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.