CODEWITHSUNDEEP
sql-server
rajusem
rajusem

Reputation: 79

SQL Query - Filter valid & invalid data by few columns

I have a table data shown as below.

enter image description here

You can generate the same by running following script 

DECLARE @Temp Table
(
	Id varchar(50),
	Name varchar(50),
	ProductId varchar(50)
)
insert into @Temp values('1','O1','P1');
insert into @Temp values('1','O1','P2');
insert into @Temp values('2','O1','P1');
insert into @Temp values('3','O1','P3');
insert into @Temp values('4','O1','P4');
insert into @Temp values('5','O1','P4');
insert into @Temp values('6','O1','P6')
select * from @Temp

I want two different out put based on below condition

1) Either Id or ProductId are not same

enter image description here

2) Either Id or ProductId are same

enter image description here

What is the best way to do this operation? Appreciate running code for the same so my input can be validate on the same?

Upvotes: 3

Views: 1408

Answers (3)

rajusem
rajusem

Reputation: 79

Using similar approach from Larnu's comment, in place of CTE using Temp tale to store the result so it can be reusable in two query. 

DECLARE @TempCount Table
(
	Id varchar(50),
	Name varchar(50),
	ProductId varchar(50),
	IdCount int,
	ProductCount int
)


insert into @TempCount
    SELECT Id,
           [Name],
           ProductId,
           COUNT(ID) OVER (PARTITION BY ID) AS IDCount,
           COUNT(ProductId) OVER (PARTITION BY ProductId) AS ProductCount
    FROM @Temp
	
--Get the unique records
SELECT *
FROM @TempCount
WHERE IDCount = 1 AND ProductCount = 1;

--Get the duplicate records
SELECT *
FROM @TempCount
WHERE IDCount > 1 OR ProductCount > 1;

Upvotes: 0

Suraj Kumar
Suraj Kumar

Reputation: 5653

You can also try this

First Query

Select * from @Temp
where Id in (
    select Id from @Temp group by Id having Count(Id) = 1
)and ProductId in(
    select ProductId from @Temp group by ProductId having Count(ProductId) = 1
)

Second Query

Select * from @Temp
where Id in (
    select Id from @Temp group by Id having Count(Id) > 1
)or ProductId in(
    select ProductId from @Temp group by ProductId having Count(ProductId) > 1
)

To see the complete query with output you can check live demo here.

Upvotes: 0

Thom A
Thom A

Reputation: 95830

I think this is what you are after:

WITH CTE AS(
    SELECT Id,
           [Name],
           ProductId,
           COUNT(ID) OVER (PARTITION BY ID) AS IDCount,
           COUNT(ProductId) OVER (PARTITION BY ProductId) AS ProductCount
    FROM @Temp)
SELECT Id,
       [Name],
       ProductId
FROM CTE
WHERE IDCount = 1 AND ProductCount = 1;

This, at least, returns the results you are after:

Id  Name ProductId
--- ---- ---------
3   O1   P3
6   O1   P6

You should easily be able to change the WHERE to get the other result; if you can't this means you don't understand the above code and therefore you should be asking how it works.

Upvotes: 4

Related Questions