Confusing group question - 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 2012 Forums

Transact-SQL (2012)

Confusing group question

Author

Topic

mike13
Posting Yak Master

219 Posts

Posted - 2014-09-02 : 14:29:37

HI all,

I got a list of orders shipped.
And i want to know (count) how many of those orders have exact the same items in each package (orderid).
So i can have people prepack those items together.

This is table structure:
T_order_main

ORDERID int
Shipdate datetime
Orderstatus int

T_Order_details

ID int
OrderID int
ProductID int
OptionID int
Quantity int

This is the select that orders all the items in the orders

SELECT TOP (100) PERCENT dbo.T_Order_Main.ORDERID, dbo.T_Order_Detail.OptionID, dbo.T_Order_Detail.Quantity
FROM dbo.T_Order_Main INNER JOIN
dbo.T_Order_Detail ON dbo.T_Order_Main.ORDERID = dbo.T_Order_Detail.OrderID
WHERE (dbo.T_Order_Main.Orderstatus = 8) AND (MONTH(dbo.T_Order_Main.Shipdate) = 9) AND (YEAR(dbo.T_Order_Main.Shipdate) = 2014)

Thanks Million

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-02 : 17:44:52

I guess you want;


select dbo.T_Order_Main.ORDERID, dbo.T_Order_Detail.OptionID, sum(dbo.T_Order_Detail.Quantity)
...
group by dbo.T_Order_Main.ORDERID, dbo.T_Order_Detail.OptionID

mike13
Posting Yak Master

219 Posts

Posted - 2014-09-03 : 04:54:24

No, that not that what i want.

I want to know count the orders that have exact the same items and quantities them.

ex:

orderid=1
1x product a
2x product b

orderid 2
1x product a

orderid 3
2x product b

orderid 4
1x product a
2x product b

so, i want to see i want to see is :
2x orders with same items(orderid 1 and orderid 2)
1x other
1x other

Do im making sense?

mike13
Posting Yak Master

219 Posts

Posted - 2014-09-10 : 12:59:35

nobody can help me this? ;-)

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-10 : 14:52:25

OK, so you can count the orders and group by order items. I'd like to post the query but I don't have your whole table definition. Can you please post it as a CREATE TABLE statement?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources