MS SQL Query to group rows and Identify those groups where the same value exists in a field for all rows

That title sounds a little crazy but here's what I'm trying to do.  

I have groups of records that can be grouped by 3 fields.  That is, if field1=field1 AND field2=field2 AND field3=field3 in two or more rows, the rows belong to the same "group".

I think I can use PARTITION BY for this (PARTITION BY field1, field2, field3)  

What I need to determine is for all of the groups, how many of them have the same value in another field (e.g., FIELD4).  For example, given:

FIELD1 | FIELD2 | FIELD3 | FIELD4
---------------------------------------------
   1             A             5             x  
   1             A             5             x  
   1             A             5             x  
   2             B             4             x  
   2             B             4             y  
   3             A             5             x

Group "1 A 5" has 'x' for all of its values in FIELD4 (this is a group I'm looking for)
Group "2 B 4" has an 'x' and a 'y' in FIELD4 (I'm not looking for this group)

Thanks in advance for any help.
LVL 1
ttist25Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BembiCEOCommented:
Just a fast idea...
You can build a GROUPED query where you group field1, field2 and field3
For field 4 you can use the count function and your result are all records with a value of 1
0
Pawan KumarDatabase ExpertCommented:
Please try full tested solution.

CREATE TABLE AND DATA GENERATION
CREATE TABLE Fielder
(
	 FIELD1 INT
	,FIELD2 VARCHAR(1)
	,FIELD3 INT
	,FIELD4 VARCHAR(1)
)
GO

INSERT INTO Fielder VALUES
(1 ,'A',5,'x'),  
(1 ,'A',5,'x'),  
(1 ,'A',5,'x'),  
(2 ,'B',4,'x'),  
(2 ,'B',4,'y'),  
(3 ,'A',5,'x')
GO

Open in new window


SOLUTION

SELECT F.*
FROM 
(
	SELECT FIELD1
	FROM Fielder
	WHERE FIELD4 = 'x'
	GROUP BY FIELD1
	HAVING COUNT(FIELD4) > 1
)k INNER JOIN Fielder F ON k.FIELD1 = f.FIELD1

Open in new window


OUTPUT
/*------------------------
OUTPUT
------------------------*/
FIELD1      FIELD2 FIELD3      FIELD4
----------- ------ ----------- ------
1           A      5           x
1           A      5           x
1           A      5           x

(3 row(s) affected)

Open in new window

0
ttist25Author Commented:
Thanks for the responses Bembi and Pawan.

Pawan, I need to group on field1, field2, and field3 - I'm not sure how to do that within your solution.

Can you help?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Pawan KumarDatabase ExpertCommented:
Yes we can do the grouping. GROUP BY FIELD1,FIELD2,FIELD3

SOLUTION

SELECT F.*
FROM 
(
	SELECT FIELD1
	FROM Fielder
	WHERE FIELD4 = 'x'
	GROUP BY FIELD1,FIELD2,FIELD3
	HAVING COUNT(FIELD4) > 1
)k INNER JOIN Fielder F ON k.FIELD1 = f.FIELD1

Open in new window


OUTPUT

/*------------------------
OUTPUT
------------------------*/
FIELD1      FIELD2 FIELD3      FIELD4
----------- ------ ----------- ------
1           A      5           x
1           A      5           x
1           A      5           x

(3 row(s) affected)

Open in new window

0
Scott PletcherSenior DBACommented:
If you only need to see those 4 columns, you can use the code below.  If you need to list additional columns, let me know and I'll adjust the code accordingly.

The other code has numerous issues.  I've adjusted the sample data to demonstrate some of those issues:

TRUNCATE TABLE Fielder;
INSERT INTO Fielder VALUES (1 ,'A',5,'x'),  (1 ,'A',5,'x'),  (1 ,'A',5,'x'),  
    (2 ,'B',4,'x'),  (2 ,'B',4,'x'),  (2 ,'B',4,'y'),  
    (3 ,'A',5,'x'),
    (4 ,'C',6,'y'), (4 ,'C',6,'y')

This lists 1,3 and 4, but you can add a COUNT(*) >=2 condition if you only want groups where there are at least 2 (or however many) rows in the group.

SELECT FIELD1, FIELD2, FIELD3, MIN(FIELD4) AS FIELD4, COUNT(*) AS Row_Count
FROM dbo.Fielder
GROUP BY FIELD1, FIELD2, FIELD3
HAVING MIN(FIELD4) = MAX(FIELD4)
ORDER BY FIELD1, FIELD2, FIELD3
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Pawan KumarDatabase ExpertCommented:
Well my first query is already giving all the columns. The last query WILL NOT GIVE you all the required rows. IT will give you first and last row of the group.
0
Pawan KumarDatabase ExpertCommented:
If you dont want to use the where clause of X then you can use below for single/multiple columns

SOLUTION

SELECT F.*
FROM 
(
	SELECT FIELD1,FIELD2,FIELD3,MAX(FIELD4) Field4
	FROM Fielder	
	GROUP BY FIELD1,FIELD2,FIELD3
	HAVING COUNT(DISTINCT FIELD4) <> COUNT(*)
	
)k INNER JOIN Fielder F 
ON k.FIELD1 = f.FIELD1 AND k.FIELD2 = f.FIELD2 AND k.FIELD3 = f.FIELD3

Open in new window


OUTPUT
/*------------------------
OUTPUT
------------------------*/
FIELD1      FIELD2 FIELD3      FIELD4
----------- ------ ----------- ------
1           A      5           x
1           A      5           x
1           A      5           x

(3 row(s) affected)

Open in new window

0
ttist25Author Commented:
Thanks guys!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

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.