counting number of occurrences of names in a table 1

[thefrstgatsby]

If I have a table called names with fields called firstn and last n with some data

firstn lastn
------ ------

joe blow
joe mcintire
amy etc
mike etc


And I want to count the number of occurences of each fname in the table, so that I would get the following results

name # of occurrences

joe 2
amy 1
mike 1

actually it doesn't even need to say the number of occurences, I just want to order the names by number of occurences, how would I do that?

 

[SQLSister]

Do you want other fields besides the fname field?

Questions about posting. See faq183-874

 

[mrdenny]

You need a simple select with a count.

select fname, count(*)
from names
group by fname
order by count(*) desc

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[SQLSister]

Denny, that's great if all he wants is the fname field. That's why I asked the question I did - becasue the solution is different depending on whether he also needs other fields.

Questions about posting. See faq183-874

 

[thefrstgatsby]

I will be relating other tables to the query, but I will onlyu be displaying the names field

 

[thefrstgatsby]

is there anyway for me to convert a unique identifier to a countable type?

 

[mrdenny]

I'm not sure what you mean by "convert a unique identifier to a countable type"?

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[thefrstgatsby]

I have a unique id key, which I need to count, but you can't count a unique identifier key, so is there anyway I can create a "temporary copy" of the key to be read and counted, based on other where criteria, and then tossed once the procedure is done?

 

[thefrstgatsby]

I need to count a unique ID but I can't because count will not work on unique ID (key). so I need to do a count(convert()) but, I haven't heard of that being done before, and I can't find it on the web. Do you know of that function?

 

[donutman]

Why can't you simply Count(AnotherColumn or *)? with the same from and where clause.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[mrdenny]

From what you are describing count(*) should work for you.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[thefrstgatsby]

no, I need to do a convert on a unique id, something like TOCHAR, but I am unfamiliar with how it works.

basicall a typical unique id might be A#A###AA-#A#A-##A#-####-########A##A

Where A represents alpha and # represents numberic.

but I need to convert that to a char variable that I can do a count on, like COUNT(TOCHAR(unique_id)) but I don't know what the syntax is.

 

[mrdenny]

count(convert(varchar(50), unique_id)) will convert a uniqueidentifier into a varchar string.

I'm still not clear on why count(*) wouldn't work instead. This method will take addational CPU and disk time.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[NHW]

SQLSister: I've currently come across a problem that you raised - what if I want to count the number of occurances of a value for different columns? Eg if my table is:

Val1 Val2
---- ----
True True
False True
True False
False True
True True

And I want to return a recordset with something like this, counting the number of "True":

CountVal1 CountVal2
--------- ---------
3 4

How should I go about writing an SP for it? Thanks!

 

[donutman]

Select SUM(Case Col1 when A then 1 else 0 end), SUM (Case Col2 when A then 1 else 0 end) etc
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[kavin]

select firstn,count(*) as TotalCount from #t1
group by firstn
order by firstn