Sql count distinct funtion 2

[Matsta]

I'm new to SQL and I need to count a unique record in the field. I tried..

select count (distinct ' ') as

but it didn't work the way I wanted it to.

Dbase example:

idnumb
88888
88888
88888
44444
44444

I need these results:

88888 3
44444 2

I know this is really easy..but I just can't get it.

Thanks!

 

[sunila7]

Hi,

Try this

Select idnumb,count(idnumb) cntIdNumb from TBL group by idnumb


Sunil

 

[Matsta]

Thanks! Perfect!!...i guess i was thinking too much about it

 

[SQLBill]

Just to let you know why your query didn't work.

Your query: "select count (distinct ' ') as " just counted the distinct whatevers. So, three instances of 8888 only count as 1, n amount of 4444 only counts as 1. Remember you are counting the DISTINCT instances.

select distinct whatever, count(whatever) as Amount
from mytable
group by whatever

Would have returned:
8888 3
4444 2

-SQLBill

 

[Matsta]

I am completely with you...thanks a million!

Maybe you could answer this for me though...I also need to assign a unique identifer to each unique field so can do a count and a comparison for instance. If i created the field cnt I would want my dbase to look like this.

idnumb cnt
88888 1
88888 1
88888 1
33333 2
33333 2

I need the count to stop at each new idnumb. I know how to do this in VFP but not sql. Any help would be wonderful. Thanks!!

Thanks!

 

[ClaireHCM]

This is one way,Try~~

1.
create table C(id int,amount int)
go
insert into C select 8888,3
insert into C select 4444,2
insert into C select 5555,5
insert into C select 1111,7
go

2.
create table D(id int identity(1,1),ids int,amount int)
go
insert into D select * from C
go

3.
create table E (idnumb int,cnt int)
go

4.
declare @id int
set @id = 1
while @id <= (select max(id) from D)
begin
declare @ids int
set @ids = (select ids from D where id = @id)
declare @amount int
set @amount = 1
while @amount <= (select amount from D where id = @id)
begin
insert into E select @ids,@id
set @amount = @amount+1
end
set @id = @id +1
end


5.Result I got

8888 1
8888 1
8888 1
4444 2
4444 2
5555 3
5555 3
5555 3
5555 3
5555 3
1111 4
1111 4
1111 4
1111 4
1111 4
1111 4
1111 4
-------------------------------------------------

Remember there is a identity(1,1) in table &quot;D&quot;

 

[Matsta]

Thanks! I will try it..

 

[Matsta]

I now got this:

select distinct whatever, count(whatever) as Amount
from mytable
group by whatever

Would have returned:
8888 3
4444 2


But...
What if I have 2 different counts that I need

8888 3 5
4444 2 3

I tried this:
select distinct whatever,whatever2 count(whatever) as Amount,
count(whatever2) as cnt from mytable
group by whatever,whatever2

How would I write it since the group by is different then in VFP??

thanks!

 

[SQLBill]

What is the second count counting? This is the only information you have given us so far:

idnumb
88888
88888
88888
44444
44444

I can see that data returning this:
8888 3
4444 2

But what kind of criteria will cause this:
8888 3 5
4444 2 3

-SQLBill

 

[Matsta]

sorry..

I'll try to explain better..
My table has say 4 fields in it. Two id #'s..one for the member and one for the provider. One quanity count, and one specgroup. Look like this

id#1 id#2 quan specgroup
444 matt 2 tp
444 matt 1 tp
888 Mary 3 pp
888 Mary 1 sy
777 joe 2 pp
222 john 1 sy


from my table I need a count of how may unique id#1's are in my table and how many unique id#2 are in my table sum(quan) all grouped by specgroup. so i need results like this

idcnt id#2 quan specgroup
1 1 3 tp
2 2 2 sy
2 2 5 pp

I hope this is better for you to understand..

Thanks!

 

[ClaireHCM]

select count(distinct id1) as idcant,count(distinct id2) as id#2,sum(quan) as quan,spec from b group by spec

 

[Matsta]

Thanks again..

The light bulb clicked I now understand and have hold of distinct and how it works. This really is a new way of thinking from VFP. Anyway it worked and I so appreciated it!
thanks again for all the help from everyone!

 

[MAK9974]

very nice