select DISTINCT and sum? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

select DISTINCT and sum?

Author

Topic

dtrance
Starting Member

10 Posts

Posted - 2005-03-01 : 14:20:28

I have an inventory table which holds duplicate part numbers because they may exist in different locations and with different quantities. I need a query were the user doesnt care where the item is, but wants to know total available.

Lets say I have an inventory table like so:

partno location qty
12233 1A 2
12233 2B 9

What would my query look like to produce

partno qty
12233 11

TIA for any suggestions.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-03-01 : 14:26:14

Select partNo, qty = sum(qty) From inventory Group BY partNo

This is pretty basic - have a look at BOL (Books Online aka SQL Help)
read up on Sum() & Group By

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

dtrance
Starting Member

10 Posts

Posted - 2005-03-01 : 14:36:56

Thanks for your help. That is exactly what I was looking for. I'll do some reading too.

dtrance
Starting Member

10 Posts

Posted - 2005-03-02 : 11:18:32

Corey

This table is a part of a view (orderitems). If I query the view to include other columns, would I use what you have showed me in a sub query? I tried the following:
SELECT orderitem_id,
(SELECT DISTINCT part_number, tot_qty = SUM(tot_qty)
FROM orderitems) AS Expr2
FROM orderitems
WHERE (part_number = 'HSK63-BZ48S-01')

It complains about using an EXIST statement, howerver when I use EXIST it returns all records. BTW I did look in Books On Line for help, but couldnt find an example for what I was looking for.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-03-02 : 15:27:19

i think this is what you want:


SELECT 
	orderitem_id,
	tot_qty = SUM(tot_qty)
FROM orderitems
WHERE part_number = 'HSK63-BZ48S-01'
Group By orderitem_id

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

dtrance
Starting Member

10 Posts

Posted - 2005-03-02 : 16:18:36

I figured that out, with each additional column I add in the SELECT statement I do so in the GROUP BY statement to get it to work. Is this proper? I will explain in more detail my original problem ....

I have an internal ordering system that is made for customers. I have three tables, one for item information and one for inventory, and one for the order. When an order is made, the ids from the item table are stored in the order table. A simple query to the order table by order number produces the actual order, however if the item exists in more than one location in the inventory table, it shows up as more than one line item in the order (with different quantities). Instead I would like it to show only one line item and sum the quantities of all records found by that part number.

originally my query looked like this:

SELECT i.part_number, i.description, i.manufacturer, o.process, o.quantity, o.order_item_status,o.comments, o.ov, i.part_number2, i.stamm_id, i.item_id, o.orderitem_id, c.tot_qty, o.proposal_date, o.wip_date, o.cancelled_date, o.account_date, o.rfq_date, o.rfs_date, o.final_date,o.pl_date, o.entry_date
FROM dbo.item i JOIN dbo.order_item o ON i.item_id = o.item_id LEFT JOIN dbo.cs_inventory c ON i.part_number = c.partno1
WHERE o.order_id = var_order_id
ORDER BY orderitem_id

Using your suggestion as reference I created a view:

SELECT dbo.item.part_number, dbo.item.manufacturer, dbo.order_item.process, dbo.item.description, dbo.order_item.quantity, dbo.cs_inventory.tot_qty,
dbo.order_item.ov, dbo.order_item.comments, dbo.order_item.entry_date, dbo.order_item.pl_date, dbo.order_item.rfs_date, dbo.order_item.rfq_date,
dbo.order_item.cancelled_date, dbo.order_item.final_date, dbo.order_item.account_date, dbo.order_item.wip_date, dbo.order_item.proposal_date,
dbo.order_item.order_id, dbo.order_item.item_id, dbo.order_item.orderitem_id, dbo.order_item.order_item_status
FROM dbo.order_item INNER JOIN
dbo.item ON dbo.order_item.item_id = dbo.item.item_id INNER JOIN
dbo.cs_inventory ON dbo.item.part_number = dbo.cs_inventory.partno1

I use this query:

SELECT part_number, manufacturer, process, description, quantity,SUM(tot_qty) AS tot_qty, ov, comments, entry_date, pl_date, rfs_date, rfq_date, cancelled_date, final_date, account_date, wip_date, proposal_date, order_id, item_id, orderitem_id, order_item_status
FROM dbo.orderitems
WHERE order_id='var_order_id' GROUP BY part_number, manufacturer, process, description, quantity, ov, comments, entry_date, pl_date, rfs_date, rfq_date, cancelled_date, final_date, account_date, wip_date, proposal_date, order_id, item_id, orderitem_id, order_item_status
ORDER BY orderitem_id

This works great for the case of producing one line item if it exists in more than one place in the inventory table. However there are no items in orders in which are suppose to have stuff. Am I taking the wrong approach to this?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources