Query for selecting a criteria

Question

Post reply

Query for selecting a criteria

chloe-1002909

Newbie

Points: 1
More actions
December 3, 2010 at 9:55 am

#232142

Hello Experts,
I can run basic queries, but need a little assistance on a more difficult query:
I have a table which contains order lines. Within this table I have the following fields which I want to use- Ordernumber and partnumber.
I want to run a query to give me the number of orders where a customer has ordered certain products together on the same order.
For example; using the sample data below - The number of orders where the customer has ordered GGT-122 and GGT-123 together - The result would be 1.
Example of data
Ordernumber PartNumber
000001 GGT-123
000002 GGT-122
000002 GGT-123
000002 GGT-127
000003 GGT-128
000003 GGT-129
I hope this makes sense and would appreciate any help!
Thank You.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

Like this?

-- See how this starts off with a table and data in it?

-- If you had provided us the data in this format,

-- it would have made things easier for all of the

-- volunteers on this site to help you out.

DECLARE @test-2 TABLE (Ordernumber varchar(6), PartNumber varchar(7));

INSERT INTO @test-2

SELECT '000001', 'GGT-123' UNION ALL

SELECT '000002', 'GGT-122' UNION ALL

SELECT '000002', 'GGT-123' UNION ALL

SELECT '000002', 'GGT-127' UNION ALL

SELECT '000003', 'GGT-128' UNION ALL

SELECT '000003', 'GGT-129';

WITH CTE AS

(

SELECT Ordernumber

FROM @test-2

WHERE Ordernumber in (select Ordernumber from @test-2 where PartNumber = 'GGT-122')

AND Ordernumber in (select Ordernumber from @test-2 where PartNumber = 'GGT-123')

GROUP BY Ordernumber

)

SELECT COUNT(*)

FROM CTE;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 2

chloe-1002909 (12/3/2010)
Hello Experts,
I can run basic queries, but need a little assistance on a more difficult query:
I have a table which contains order lines. Within this table I have the following fields which I want to use- Ordernumber and partnumber.
I want to run a query to give me the number of orders where a customer has ordered certain products together on the same order.
For example; using the sample data below - The number of orders where the customer has ordered GGT-122 and GGT-123 together - The result would be 1.
Example of data
Ordernumber PartNumber
000001 GGT-123
000002 GGT-122
000002 GGT-123
000002 GGT-127
000003 GGT-128
000003 GGT-129
I hope this makes sense and would appreciate any help!
Thank You.

Hi,

Is this what you are looking for

I want to run a query to give me the number of orders where a customer has ordered certain products together on the same order.

here number of orders is count you are asking

if so

DECLARE @test-2 TABLE (Ordernumber varchar(6), PartNumber varchar(7));

INSERT INTO @test-2

SELECT '000001', 'GGT-123' UNION ALL

SELECT '000002', 'GGT-122' UNION ALL

SELECT '000002', 'GGT-123' UNION ALL

SELECT '000002', 'GGT-127' UNION ALL

SELECT '000003', 'GGT-128' UNION ALL

SELECT '000003', 'GGT-129';

Select Ordernumber,COUNT(Ordernumber)[number of orders]

from @test-2

group by

Ordernumber

Thanks

Parthi

Thanks
Parthi

GSquared SSC Guru Points: 260824 More actions · Answer 3

select OrderNumber

from @test-2

where PartNumber = 'GGT-122'

intersect

select OrderNumber

from @test-2

where PartNumber = 'GGT-123';

That will give you the ones where they've ordered both.

It easily expands to include a variety of complex combinations, using the Intersect statement.

You can use that in a CTE or derived table, and then query the count.

;with Orders as

(select OrderNumber

from @test-2

where PartNumber = 'GGT-122'

intersect

select OrderNumber

from @test-2

where PartNumber = 'GGT-123')

select count(*)

from Orders;

I've found this Intersect method to work quite well on this kind of thing.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

sri76 Say Hey Kid Points: 666 More actions · Answer 4

Hi,

you can get using the below query also

Select Ordernumber from test Where Ordernumber in

(Select Ordernumber From test Where PartNumber = 'GGT-122') and PartNumber ='GGT-123'