Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Select -

Posted on 2016-10-06
8
Medium Priority
?
66 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
8 Comments
 
LVL 52

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 66

Assisted Solution

by:Jim Horn
Jim Horn earned 1000 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 32

Accepted Solution

by:
Pawan Kumar earned 1000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 32

Expert Comment

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

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 52

Expert Comment

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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

604 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