Solved

SQL Select -

Posted on 2016-10-06
8
58 Views
Last Modified: 2016-10-28
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
0
Comment
Question by:tmajor99
  • 3
  • 2
8 Comments
 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 41831745
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
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
ID: 41831746
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
 
LVL 28

Accepted Solution

by:
Pawan Kumar earned 250 total points
ID: 41831762
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41833010
@Author - Appreciate any feedback on the above .
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41853331
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
 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 41853339
Oh, right. Is the DISTINCT keyword that Jim has in his solution.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question