Find a SQL statement ~~~ Master help ~~~

cl1985111

Records in the table allip

id ip sid
1 127.0.0.1 a
2 127.0.0.2 a
3 127.0.0.3 a
4 127.0.0.1 a
5 127.0.0.4 b
6 127.0.0.1 a
7 127.0.0.5 c
8 127.0.0.6 d

Now I find the sum of the number of all unique IPs with sid = a (that is, an IP can only appear once)
The resulting value should be 3

I use
select count (ip) as c from allip where sid = 'a' group by ip
The answer is the number of each IP instead of their sum. How to write SQL? Ask for advice ~~~~~~~~~~~~~~~~

michealhenry

select count (distinct ip) as c from allip where sid = 'a'

fanfan1980

select count (distinct ip) as c from allip where sid = 'a' group by ip

我是杀人狂

select count (ip) as c from allip where sid = 'a' and ip in (select distinct ip from allip)

雪兽冰牧

--Test Data
create table t (id int identity (1,1), ip nvarchar (20), sid char (1))
insert t (ip, sid)
select '127.0.0.1', 'a' union all
select '127.0.0.2', 'a' union all
select '127.0.0.3', 'a' union all
select '127.0.0.1', 'a' union all
select '127.0.0.4', 'b' union all
select '127.0.0.3', 'a' union all
select '127.0.0.5', 'c' union all
select '127.0.0.6', 'd'
select count (distinct ip) from t where sid = 'a'
drop table t

hiqwolfzzh

select count (distinct ip) from #temp where sid = 'a'

wgfxman

select count (ip) from (select ip from allip where sid = 'a' group by ip having count (ip) = 1) m

gaoqingfeng

Learn it!

wgfxman

select count (distinct ip) from allip where sid = 'a'