HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

SQL Trying to sum and group by distinct values

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
distinctvaluesgroupsqltryingsumand

Problem

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.

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 LineName

Code 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 LineName

Context

StackExchange Database Administrators Q#267630, answer score: 2

Revisions (0)

No revisions yet.