Need help writing an SQL query to count non duplicate rows (not a distinct count)

Question

I have a table like below. I'm trying to do a count of IDs that are not duplicated. I don't mean a distinct count. A distinct count would return a result of 7 (a, b, c, d, e, f, g). I want it to return a count of 4 (a, c, d, f). These are the IDs that do not have multiple type codes. I've tried the following queries but got counts of 0 (the result should be a count in the millions).

select ID, count (ID) as number
from table
group by ID
having count (ID) = 1

Select count (distinct ID)
From table
Having count (ID) = 1

ID|type code
a|111
b|222
b|333
c|444
d|222
e|111
e|333
e|555
f|444
g|333
g|444

thanks to @scaisEdge! The first query you provided gave me exactly what I'm looking for in the above question. Now that that's figured out my leaders have asked for it to be taken a step further to show the count of how many times there is an ID within a single type code. For example, we want to see

type code|count 111|1 222|1 444|2

There are 2 instances of IDs that have a single type code of 444 (c, f), there is one instance of an ID that has a single type code of 111 (a), and 222 (d). I've tried modifying the query as such, but have been coming across errors when running the query

select count(admin_sys_tp_cd) as number from ( select cont_id from imdmadmp.contequiv group by cont_id having count(*) =1) t group by admin_sys_tp_cd

ScaisEdge · Accepted Answer · 2016-07-25 20:12:51Z

1

If you want the count Could be

select count(*) from (
  select id from 
  my_table 
  group by id 
  having count(*) =1 
) t

if you want the id

  select id from 
  my_table 
  group by id 
  having count(*) =1

answered Jul 25, 2016 at 20:12

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

AVK Over a year ago

What is different between count(*) and Count(ID)? Can you explain if you don't mind me asking?

Kostya Over a year ago

mostly the same, only when ID is null it wont get counted.

ScaisEdge Over a year ago

@AVKNaidu This link could help you percona.com/blog/2007/04/10/count-vs-countcol .. essentially count(*) count the number of row satisfying the where condition, count(ID) count the mber the rows with id (not null) that satisfying the where condition

AVK Over a year ago

thanks @scaisEdge, I created few dummy tables and tried this with nulls and stuff yesterday. didn't knew * and colName makes this huge difference. Thanks for the link btw. Bookmarked it.

ScaisEdge Over a year ago

@AVKNaidu Well if you find my answer and my comment useful ..please rate it

Gabriel Leicand · Accepted Answer · 2016-07-25 20:58:13Z

-2

Hou about this you do a loop in a temporary table?:

select 
* 
into #control
from tablename 

declare @acum as int
declare @code as char(3)
declare @id as char(1)
declare @id2 as int
select @acum=0
while exists (select* from #control)
begin
    select @code = (select top 1 code from #control order by id)
    select @id = (select top 1 id from #control order by id)
    select @id2 =count(id) from #control where id in (select id from tablename where id = @id and code <> @code)
    if @id2=0 
    begin
        select @acum = @acum+1
    end

    delete #control
        where id = @id --and code = @code
end
drop table #control
print @acum

answered Jul 25, 2016 at 20:58

Gabriel Leicand

1

Collectives™ on Stack Overflow

Need help writing an SQL query to count non duplicate rows (not a distinct count)

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related