CODEWITHSUNDEEP
sqlgroup-by
otto.kranz
otto.kranz

Reputation: 91

SQL filter in group

I have a database with one Table:

+-------+------+--+
| Col1  | Col2 |  |
+-------+------+--+
| Auto  | alt  |  |
| Auto  | alt  |  |
| Auto  | neu  |  |
| Haus  | alt  |  |
| Haus  | alt  |  |
| Stuhl | neu  |  |
+-------+------+--+

Now I want to get all groups from Col1 with more than one repition like Auto and Haus. I did that with:

SELECT talbe1.Col1, talbe1.Col2
FROM table1
WHERE talbe1.Col1 In (SELECT Col1 FROM talbe1.Col1 As Tmp GROUP BY Col1 HAVING Count(Col1)>1)
ORDER BY talbe1.Col1;

Additionally I now want these groups only when the values in Col2 are NOT all the same like in Auto. The result would look like this:

+------+------+--+
| Col1 | Col2 |  |
+------+------+--+
| Auto | alt  |  |
| Auto | alt  |  |
| Auto | neu  |  |
+------+------+--+

Can someone help me how to do that in SQL?

Upvotes: 2

Views: 78

Answers (3)

Jay Shankar Gupta
Jay Shankar Gupta

Reputation: 6088

SELECT t3.Col1,t3.Col2
FROM table1 t3
INNER JOIN
(
SELECT t1.Col1
FROM (SELECT Col1,Col2,COUNT(*) AS cn FROM table1 GROUP BY Col1,Col2) t1
INNER JOIN
(
SELECT Col1,COUNT(*) AS cn
FROM table1
GROUP BY Col1
HAVING COUNT(*) > 1
) t2
ON t1.Col1=t2.Col1 AND t1.cn != t2.cn
GROUP BY t1.Col1
) t4
ON t3.col1=t4.col1

OUTPUT

Col1    Col2
Auto    alt
Auto    alt
Auto    neu

Demo

http://sqlfiddle.com/#!9/24b3ec/33

Upvotes: 0

F. Valeriy
F. Valeriy

Reputation: 11

You can use a subquery to find the values in the column that satisfy the condition, and then get the rest of the necessary information:

DECLARE @Table TABLE (col1 VARCHAR(max), col2 VARCHAR(max)) 

INSERT INTO @Table (col1, col2)
values
('Auto', 'alt'),
('Auto', 'alt'),
('Auto', 'neu'),
('Haus', 'alt'),
('Haus', 'alt'),
('Stuhl', 'neu')

SELECT *
FROM @Table
WHERE col1 IN (
    SELECT col1
    FROM @Table
    GROUP BY col1
    HAVING COUNT(*) > 1 
    AND COUNT(DISTINCT col2) > 1 
)

Where is the condition:

HAVING COUNT(*) > 1

Find all duplicate values in column col1

And this condition:

AND COUNT(DISTINCT col2) > 1

Find those records in which column col2 has non-unique values

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You could use exists 

select * from table t
where exists (select 1 from table 
              where Col1 = t.Col1 
              group by Col1 
              having count(distinct Col2) > 1);

Upvotes: 1

Related Questions