patternsqlMinor
SQL Trying to sum and group by distinct values
Viewed 0 times
distinctvaluesgroupsqltryingsumand
Problem
I have a table
I am trying to count the number of different materials, and sum the quantities along the LineName, so that I get something like this:
Instead, when I use a combination of count distinct, sum and group by, I get an incorrect result:
How should I do this correctly? I've tried with
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.
+-------+----------+------------+----------+---------+
| 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 LineNameBut 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.
Solution
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 LineNameCode Snippets
WITH Dist
AS (SELECT DISTINCT LineName,
Material,
ProdQty
FROM [table])
SELECT LineName,
COUNT(Material) AS Cmat,
SUM(ProdQty) AS ProdQty
FROM Dist
GROUP BY LineNameContext
StackExchange Database Administrators Q#267630, answer score: 2
Revisions (0)
No revisions yet.