cursor update

Question

cursor update

ksexton

SSCrazy

Points: 2790
More actions
September 19, 2002 at 1:54 pm

#158498

I need to get a count of each distinct value in all the columns in the publishers table.
I think the only way to do this is write some type of cursor. Please help.
-k

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 1

quote:
I need to get a count of each distinct value in all the columns in the publishers table.
I think the only way to do this is write some type of cursor. Please help.
-k

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 2

Well this work for you.

use pubs

select count(distinct(pub_id)),

count(distinct(pub_name)) ,

count(distinct(city)),

count(distinct(state)),

count(distinct(country))

from publishers

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

ksexton SSCrazy Points: 2790 More actions · Answer 3

I did not state my request clearly. I need the frequency of each DISTINCT value.

Ex: If column_gender has 2 distinct values (M,Y) I need to find out there are 36 M's and 141 Y's.

So...I need to find out all the DISTINCT values and then get a count on each one of those values. I wanted help writing the cursor. ???

Thanks 🙂

ksexton SSCrazy Points: 2790 More actions · Answer 4

here is what I have so far but it does not work:

DISTINCT_COLUMNS is a table I made with a list of columns I want to find the distinct values for.

alter procedure usp_loop_columns

AS

DECLARE @c_COLUMN_NAME VARCHAR(30),

@sql varchar (300)

set @sql=

'INSERT INTO DISTINCT_COLUMNS (DISTINCT_VALUE,COLUMN_NAME)

SELECT DISTINCT ('+@c_COLUMN_NAME+'),'+@c_COLUMN_NAME+' FROM PUBLISHERS'

DECLARE @C_COL_CURSOR CURSOR

--set nocount on

--BEGIN

SET @C_COL_CURSOR= cursor for

select COLUMN_NAME from DISTINCT_COLUMNS

open @C_COL_CURSOR

fetch next from @C_COL_CURSOR into @c_COLUMN_NAME

While (@@FETCH_STATUS = 0)

BEGIN

EXEC (@SQL)

--print (@SQL)

Fetch Next From @C_COL_CURSOR into @c_COLUMN_NAME

End

Close @C_COL_CURSOR

Deallocate @C_COL_CURSOR

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 5

I'm not exactly sure what you wanted to do. But I think I changed your code to reflect what I think you wanted (see below).

I noticed your code in variable @sql was not being set because you set it early on in your code prior to having defined a value for @c_column_name. When you try to use a variable in a set statement prior to it being initialized you get very strange results. So I moved this line of code down into the while loop. Also I made a few other changes. Let me know if this is what you wanted to do. Good Luck....

use pubs

create table DISTINCT_COLUMNS (distinct_value varchar(20), column_name varchar(20), Value_Cnt int)

create table Columns_to_look_at( column_name varchar(20))

go

insert into columns_to_look_at values('country')

insert into columns_to_look_at values('state')

go

create procedure usp_loop_columns

AS

DECLARE @c_COLUMN_NAME VARCHAR(30),

@sql varchar (300)

DECLARE @C_COL_CURSOR CURSOR

--set nocount on

--BEGIN

SET @C_COL_CURSOR= cursor for

select COLUMN_NAME from columns_to_look_at

open @C_COL_CURSOR

fetch next from @C_COL_CURSOR into @c_COLUMN_NAME

While (@@FETCH_STATUS = 0)

BEGIN

set @sql=

'INSERT INTO DISTINCT_COLUMNS (DISTINCT_VALUE,COLUMN_NAME,Value_Cnt)

SELECT DISTINCT ('+@c_COLUMN_NAME+'),'''+@c_COLUMN_NAME+''',count(*) FROM PUBLISHERS' +

' group by '+ @c_column_name

EXEC (@SQL)

print (@SQL)

Fetch Next From @C_COL_CURSOR into @c_COLUMN_NAME

End

Close @C_COL_CURSOR

Deallocate @C_COL_CURSOR

go

usp_loop_columns

go

select * from distinct_columns

drop table columns_to_look_at

drop table distinct_columns

drop proc usp_loop_columns

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

jwiner SSCrazy Points: 2241 More actions · Answer 6

If I understand you correctly, there is a much easier way to do it:

Column Gender has values 'M' and 'F'

SELECT Gender , COUNT(Gender ) AS 'COUNT' FROM

ClientGender

GROUP BY Gender

If there are 4 F and 3 M the results would be:

GENDER'COUNT'

F4

M3

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 7

Yep! I was goind to suggest that, but thought maybe you wanted to build a table contain all columns, there distinct values, and a Count of each value.

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

ksexton SSCrazy Points: 2790 More actions · Answer 8

Greg,

Thanks a lot. That was exactly what I was looking for.

-K