Select "field" if columnB has >2 distinct values

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

General SQL Server Forums

New to SQL Server Programming

Select "field" if columnB has >2 distinct values

Author

Topic

MrSmith
Starting Member

5 Posts

Posted - 2013-07-02 : 13:18:54

I am very new to SQL, first time building a database. I wish to select a product if that product has multiple customers.

For instance:
Row ProductID CustomerID
1 a 1
2 b 1
3 a 2
4 b 1
5 c 3
6 c 2

Given table above I wish to display rows 1, 3, 5 & 6. To clarify products a and c have multiple customers (ProdID a has custID 1 & 2 etc.). I am not really sure where to begin, any help would be great!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-02 : 13:31:03

These are couple of different ways to do this - there may be others:


SELECT a.Row,a.ProductId, a.CustomerId
FROM YourTable a
CROSS APPLY 
(
	SELECT ProductId FROM YourTable b 
	WHERE b.ProductId = a.ProductId
	GROUP BY ProductId HAVING COUNT(DISTINCT b.CustomerId) > 1
)b

SELECT a.Row,a.productId, a.customerid
FROM YourTable a
WHERE EXISTS
(	SELECT 1 FROM YourTable b
	WHERE b.ProductId = a.ProductId
	GROUP BY ProductId HAVING COUNT(DISTINCT b.CustomerId) > 1
)

MrSmith
Starting Member

5 Posts

Posted - 2013-07-02 : 14:16:45

Thanks for the help. I was thinking I should be grouping by Customer ID so changed the last part of option b. When I run the code there is no error msg, but it's not returning any results. What I have is below, is something incorrect?

SELECT tblOrders.[Product ID], tblOrders.[Company Name], tblOrders.[Customer ID], tblOrders.[Cost], tblOrders.[Price], tblOrders.[Gross Margin]
FROM tblOrders
WHERE EXISTS
(SELECT 1 FROM qryProducts
WHERE qryProducts.[Product ID] = tblOrders.[Product ID]
GROUP BY [Customer ID] HAVING COUNT(*) >1);

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-02 : 14:38:03

Do you need the qryProducts table at all? Your query seems "upside down". I would think what you need is something like this:

SELECT  t.[Product ID] ,
        t.[Company Name] ,
        t.[Customer ID] ,
        t.[Cost] ,
        t.[Price] ,
        t.[Gross Margin]
FROM    tblOrders t
WHERE   EXISTS ( SELECT 1
                 FROM   tblOrders t2
                 WHERE  t.[Product ID] = t2.[Product ID]
                 GROUP BY t2.[Product ID]
                 HAVING COUNT(DISTINCT t2.CustomerId) > 1 );

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-02 : 14:59:42

[code]WITH cteSource(Row, ProductID, CustomerID, Yak)
AS (
SELECT Row,
ProductID,
CustomerID,
COUNT(*) OVER (PARTITION BY ProductID) AS Yak
FROM dbo.Table1
)
SELECT Row,
ProductID,
CustomerID
FROM cteSource
WHERE Yak >= 2;[/code]

N 56°04'39.26"
E 12°55'05.63"

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-02 : 15:22:05

quote:

Originally posted by SwePeso

WITH cteSource(Row, ProductID, CustomerID, Yak)
AS (
	SELECT	Row,
		ProductID,
		CustomerID,
		COUNT(*) OVER (PARTITION BY ProductID) AS Yak
	FROM	dbo.Table1
)
SELECT	Row,
	ProductID,
	CustomerID
FROM	cteSource
WHERE	Yak >= 2;

N 56°04'39.26"
E 12°55'05.63"

This is wrong. It gives incorrect results for the sample that OP posted originally because of the presence of tow rows with the same product id and customer id (rows 2 and 4 in his sample data).

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-02 : 20:26:37

And while I am talking about wrong queries, my queries also don't work right unless you replace COUNT(*) with COUNT(DISTINCT CustomerId). See corrections above in red.

chbala85
Starting Member

49 Posts

Posted - 2013-07-03 : 01:52:47

Try it .

select * from table_name where rowid in(
select Rowid from table_name
GROUP BY ProductID IDHAVING COUNT(ProductID) >1))

chbala85
Starting Member

49 Posts

Posted - 2013-07-03 : 01:53:24

select * from table_name where rowid in(
select Rowid from table_name
GROUP BY ProductID HAVING COUNT(ProductID) >1))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-03 : 02:04:43

[code]
SELECT t.[Product ID] ,
t.[Company Name] ,
t.[Customer ID] ,
t.[Cost] ,
t.[Price] ,
t.[Gross Margin]
FROM tblOrders t
CROSS APPLY( SELECT MIN(CustomerID) AS MinVal,
MAX(CustomerID) AS MaxVal
FROM tblOrders
WHERE [Product ID] = t.[Product ID]
)t2
WHERE MinVal <> MaxVal
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources