SQL Select -

I need a SQL Select that would identify attribute value discrepancies within groups.   For example, I need to find all records within a group ID that different colors.  

Here is what my SQL table looks like:

Group ID   Product ID    Color
    1                300                Blue
    1                391                Blue
    1                237                Yellow
    2                100                Black
    3                500                Green
    3                501                Green
    4                921                Blue
    4                834                        
    4                512                Red

The expected results would be list all groups that contain color discrepancy within the group.

    1                300                Blue
    1                391                Blue
    1                237                Yellow
    4                921                Blue
    4                834                        
    4                512                Red
tmajor99Asked:
Who is Participating?
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
Try this..

CREATE TABLE Colors
(
	   GroupID INT
	,  ProductID INT
	,  Color VARCHAR(30)
)
GO

INSERT INTO Colors VALUES
  (  1,                300                ,'Blue'), 
  (  1 ,               391                ,'Blue'), 
  (  1  ,              237                ,'Yellow'), 
  (  2  ,              100                ,'Black'), 
  (  3  ,              500                ,'Green'), 
  (  3  ,              501                ,'Green'), 
  (  4  ,              921                ,'Blue'), 
  (  4  ,              834                ,''),         
  (  4  ,              512                ,'Red') 
GO

--SOLUTION 1

SELECT GroupID, ProductID, Color FROM 
(
	SELECT GroupID, ProductID, Color , COUNT(*) OVER (PARTITION BY GroupID) c , COUNT(*) OVER (PARTITION BY GroupID,Color) ds
	FROM Colors
)t
WHERE c <> ds

--SOLUTION 2

SELECT c.*
FROM Colors c
CROSS APPLY
(
	SELECT GroupID, COUNT(DISTINCT Color) cnt
	FROM Colors 
	GROUP BY GroupID
	HAVING COUNT(DISTINCT Color) > 1
)x
WHERE x.GroupID = c.GroupID

--

Open in new window


Output

------------------------

GroupID      ProductID      Color
1              300                      Blue
1              391                      Blue
1              237                     Yellow
4             834      
4             921                    Blue
4              512                    Red
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Try this:
;WITH CTE_ColorGroup AS
(
	SELECT GroupID, Color, Count(1)
	FROM TableName
	GROUP BY GroupID, Color
	HAVING Count(1) > 1
)
SELECT T.*
FROM TableName T
	INNER JOIN CTE_ColorGroup G ON G.GroupID=T.GroupID

Open in new window

0
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
Assuming 'Color discrepancy' means 'return all rows where for a given Group ID there are multiple Colors'...
SELECT yt.GroupID, yt.ProductID, yt.Color
FROM YourTable yt
   JOIN (
      SELECT GroupID, COUNT(DISTINCT Color) 
      FROM YourTable
      GROUP BY GroupID
      HAVING COUNT(DISTINCT Color) > 1) ytd ON yt.GroupID = ytd.GroupID
ORDER BY yt.GroupID, yt.ProductID

Open in new window

0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Pawan KumarDatabase ExpertCommented:
@Author - Appreciate any feedback on the above .
0
 
Pawan KumarDatabase ExpertCommented:
Vitor Montalvão's solution is Incorrect.

CREATE TABLE Colors
(
	   GroupID INT
	,  ProductID INT
	,  Color VARCHAR(30)
)
GO

INSERT INTO Colors VALUES
  (  1,                300                ,'Blue'), 
  (  1 ,               391                ,'Blue'), 
  (  1  ,              237                ,'Yellow'), 
  (  2  ,              100                ,'Black'), 
  (  3  ,              500                ,'Green'), 
  (  3  ,              501                ,'Green'), 
  (  4  ,              921                ,'Blue'), 
  (  4  ,              834                ,''),         
  (  4  ,              512                ,'Red') 
GO

;WITH CTE_ColorGroup AS
(
	SELECT GroupID, Color, Count(1) [b]cnt[/b]
	FROM Colors
	GROUP BY GroupID, Color
	HAVING Count(1) > 1
)
SELECT T.*
FROM Colors T
	INNER JOIN CTE_ColorGroup G ON G.GroupID=T.GroupID

Open in new window


Incorrect output
----

GroupID      ProductID      Color
1               300                 Blue
1                391         Blue
1               237                Yellow
3       500                Green
3               501                Green
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Oh, right. Is the DISTINCT keyword that Jim has in his solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.