Frequency - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Frequency

Author

Topic

Knarf180
Starting Member

42 Posts

Posted - 2004-11-02 : 11:34:15

Is it possible for SQL to figure out the frequency of a field value in a table?

Lets say my records held the following in a given field:
10
14
10
13
13
11

I would want the query to return the number plus the frequency it accurse.. IE:
10 2
11 1
13 2
14 1

I know this can be done with cursors and such, but I would end up running a heck of a lot of querys for something that could possibly have a simple answer. Figured I'd ask..

Thanks a bunch
- Frank

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-02 : 11:36:55

cursors? Wow -- do people really write cursors for this stuff??

this is SQL 101 stuff -- read up on SELECT statemetns and GROUP BY:

select column, count(*)
from table
group by column

- Jeff

Knarf180
Starting Member

42 Posts

Posted - 2004-11-02 : 11:49:45

Geez, I've been drowning in such complicated Queries the passed few days that I'm losing touch with the basics. Thanks for clearing my head.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-02 : 13:46:21

hmmm

create table #data (i int)
insert #data select 10
insert #data select 14
insert #data select 10
insert #data select 13
insert #data select 13
insert #data select 11
create table #a (i int, cnt int)

declare @i int
declare x cursor for select * from #data
open x

fetch next from x into @i
while @@FETCH_STATUS = 0
begin
update #a set cnt = cnt + 1 where i = @i
if @@rowcount = 0
insert #a select @i, 1
fetch next from x into @i
end

close x
deallocate x
select * from #a
drop table #a

I'm waiting for someone t odo a performance comparison :).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources