x

Trying to write a SQL query to display only the “part number” with multiple warehouse “locations”

Trying to write a SQL query to display only the "part number" with multiple warehouse "locations". I want a search that will display each "partno" that has more than one "boxSize" and/or "boxLocation". There is only one table where the data that I need resides.

SELECT

DISTINCT partno, COUNT(partno) AS MULTIPLE_PARTNO

FROM [Reports].[dbo].[Data_all]

GROUP BY partno

HAVING COUNT(partno) <> '1'

more ▼

asked Jul 27, 2013 at 01:39 AM in Default

avatar image

AngelSun
0 1 1 2

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Great question! Try This:

SELECT partno, boxSize, Count(partno) as MULTIPLE_PARTNO
FROM [Reports].[dbo].[Data_all]
WHERE partno IN
(SELECT partno FROM [Reports].[dbo].[Data_all] GROUP BY partno HAVING COUNT (partno) >1)

This is assuming that PartNo only appears more then once when it has more then 1 boxSize.

Let me know if that works.

more ▼

answered Jul 28, 2013 at 06:26 AM

avatar image

eaglescout
720 5 8 12

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2221
x1208

asked: Jul 27, 2013 at 01:39 AM

Seen: 777 times

Last Updated: Jul 28, 2013 at 06:30 AM

Copyright 2018 Redgate Software. Privacy Policy