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
Go to Top of Page

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?
Go to Top of Page

mike13
Posting Yak Master

219 Posts

Posted - 2014-09-10 : 12:59:35
nobody can help me this? ;-)
Go to Top of Page

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?
Go to Top of Page
   

- Advertisement -