Solved

SQL Select -

Posted on 2016-10-06
8
43 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 46

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 23

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 23

Expert Comment

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

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 46

Expert Comment

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

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

929 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now