CODEWITHSUNDEEP
mysqlsqldatabase
Sandro21
Sandro21

Reputation: 311

How to check if a certain value is existent or if it exists twice?

So i have a table that looks something like this:

ID  CUR_Valid
1   N 
1   N
1   N
1   N
1   N
1   Y
2   N
2   N
2   Y
3   Y

So as you can see one ID can only be allowed to have one y. I want to write a statment that shows me the IDs that have no Y or more then one Y.

My code so far looks like this:

Select [BILL_ID]
      ,[Bill_MONTH]
      ,[CUR_VALID]
      ,COUNT(CUR_VALID)
FROM DB.dbo.table
WHERE CUR_VALID = 'Y'
GROUP BY [BILL_ID]
      ,[Bill_MONTH]
      ,[CUR_VALID]
 Having COUNT(CUR_VALID) > 1 OR COUNT(CUR_VALID) <1

I'm not sure whats wrong. Can someone point me into the right direction?

Upvotes: 1

Views: 75

Answers (4)

Radim Bača
Radim Bača

Reputation: 10701

It is possible to use self join like this

select d1.id, count(d2.id)
from (select id from dat group by id) d1
left join dat d2 on d1.id = d2.id and d2.cur_valid = 'Y'
group by d1.id
having count(d2.id) <> 1

demo - thanks @Abbennett for the data

Upvotes: 1

Ab Bennett
Ab Bennett

Reputation: 1432

here is a sample query that demonstrates the identification of one with no Y or one with two or more Y's 

with dat
as
(
select 1 id,'N' cur_valid  union all 
select 1,'N' union all
select 1,'N' union all
select 1,'N' union all
select 1,'N' union all
select 1,'Y' union all
select 2,'N' union all
select 2,'N' union all
select 2,'Y' union all
select 3,'Y' union all
select 4,'Y' union all /* two Ys */
select 4,'Y' union all
select 5,'N' /* no Y */)
select id
from
(
   select id,sum(case when cur_valid='Y' then 1 else 0 end) x
   from dat
   group by id
) ilv
where x<>1

this can be condensed to

with dat
as
(
select 1 id,'N' cur_valid  union all 
select 1,'N' union all
select 1,'N' union all
select 1,'N' union all
select 1,'N' union all
select 1,'Y' union all
select 2,'N' union all
select 2,'N' union all
select 2,'Y' union all
select 3,'Y' union all
select 4,'Y' union all /* two Ys */
select 4,'Y' union all
select 5,'N' /* no Y */)
select id
from dat
group by id
having sum(case when cur_valid='Y' then 1 else 0 end)<>1

Upvotes: 1

DineshDB
DineshDB

Reputation: 6193

Try this answer. Hope this helps you:

Select [BILL_ID]
      ,[Bill_MONTH]
      ,[CUR_VALID]
      ,COUNT(CUR_VALID)
FROM DB.dbo.table
--WHERE CUR_VALID = 'Y'
GROUP BY [BILL_ID]
      ,[Bill_MONTH]
      ,[CUR_VALID]
 Having COUNT(CASE WHEN CUR_VALID = 'Y' THEN 1 END) > 1 OR COUNT(CASE WHEN CUR_VALID = 'Y' THEN 1 END) <1

When you put WHERE CUR_VALID = 'Y' condition, it remove the records which don't have the the Y.

Try this update for, number of IDs.

Select COUNT(1)[No of Ids]
FROM DB.dbo.table
GROUP BY [BILL_ID]
      ,[Bill_MONTH]
      ,[CUR_VALID]
 Having COUNT(CASE WHEN CUR_VALID = 'Y' THEN 1 END) > 1 OR COUNT(CASE WHEN CUR_VALID = 'Y' THEN 1 END) <1

It returns the Number of Ids.

Upvotes: 1

PSA
PSA

Reputation: 361

use 

SQL Aliases

Count as totalValid

Select [BILL_ID]
          ,[Bill_MONTH]
          ,[CUR_VALID]
          ,COUNT(CUR_VALID) as totalValid
    FROM DB.dbo.table
    WHERE CUR_VALID = 'Y'
    GROUP BY [BILL_ID]
          ,[Bill_MONTH]
          ,[CUR_VALID]
     Having  totalValid > 1 OR  totalValid <1

Upvotes: 1

Related Questions