Solved

SQL Select -

Posted on 2016-10-06
8
53 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 48

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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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 48

Expert Comment

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

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

766 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