Returning a SUM from DISTINCT columns in Microsoft SQL

Question

Still fairly new to SQL and have been learning as I go. Hopefully I can learn something new from the wise wizards here at StackOverflow.

What I have: I have a table in which I need to run a query on. The table in total has 6 columns (including primary key).

|  ID  |  orderNumber  |  partNumber  |  name  |  qty  |  unitPrice  |

Fairly self-explanatory so far.

What I need to do: I am needing to query this table to get the SUM of the qty for each partNumber. This is to get a current on-hand of products.
Using my basic knowledge (and some Google-fu) I thought I could do something like

SELECT DISTINCT partNumber, name, SUM(qty) as qty  
FROM [Bulk]  
GROUP BY partNumber, name  
ORDER BY partNumber 

But this still returns duplicate partNumbers with that order's individual qty, like so:

ID | partNumber | name          | qty
---|------------|---------------|--------
1  | 428154     | '22" Monitor' | 5
1  | 428154     | '22" Monitor' | 8
1  | 428154     | '22" Monitor' | 2
1  | 428154     | '22" Monitor' | 4
1  | 428154     | '22" Monitor' | 1

I've read a bit about doing a PARTITION, but am not sure if that is what I need, or how to even implement that. Could someone help me out or point me in the right direction?

SAMPLE ROWS

ID | orderNumber   | partNumber  | name           | qty
---|---------------|-------------|----------------|-------
1  | 45751         | 104579      | Wireless Mouse | 10  
2  | 45718         | 104579      | Wireless Mouse | 5  
3  | 45795         | 104579      | Wireless Mouse | 3  
4  | 44354         | 104631      | Keyboard       | 18  
5  | 49765         | 104631      | Keyboard       | 1  
6  | 48789         | 104631      | Keyboard       | 9  

DESIRED OUTPUT

partNumber | name             | qty
-----------|------------------|--------
104579     | Wireless Mouse   |  18  
104631     | Keyboard         |  28  

RESULTS WITHOUT DISTINCT

partNumber | name             | qty  
-----------|------------------|--------  
464968621  | Footstand        |  1  
464968621  | Footstand        |  12   
538898644  | Wall Mount       |  12   
564848989  | IP Phone         |  4   
564848989  | IP Phone         |  1   

Answer 1

Remove DISTINCT from your query. It should look like this:

SELECT
   partNumber,
   name,
   SUM(qty) as qty  
FROM
   [Bulk]  
GROUP BY
   partNumber,
   name  
ORDER BY
   partNumber

If, as you say, the names for the same part may differ and you don't care which name is returned, you can simply do this:

SELECT
   partNumber,
   MAX(name) as name,
   SUM(qty) as qty  
FROM
   [Bulk]  
GROUP BY
   partNumber
ORDER BY
   partNumber

Using your own data:

CREATE TABLE #Bulk
(ID int, orderNumber INT, partNumber INT, name VARCHAR(30), qty INT)

INSERT INTO #Bulk
VALUES
(1,45751,104579,'Wireless Mouse',10),
(2,45718,104579,'Wireless Mouse',5),
(3,45795,104579,'Wireless Mouse',3),
(4,44354,104631,'Keyboard',18),
(5,49765,104631,'Keyboard',1),
(6,48789,104631,'Keyboard',9);

SELECT
   partNumber,
   name,
   SUM(qty) as qty  
FROM
   [#Bulk]  
GROUP BY
   partNumber,
   name  
ORDER BY
   partNumber;

Output: