show count in 2nd field?

• mick burden

  SSCarpal Tunnel

  Points: 4432

  More actions

  October 29, 2010 at 9:24 am

  #143957

  What I'm after is to be able to show distinct of entries in the first colomn and in the second colomn a count of how many times the first column appears in the table?

  as an example:

  field1

  jim

  john

  roger

  jim

  jim

  to show as

  field1 field2

  jim 3

  john 1

  roger 1

• WayneS

  SSC Guru

  Points: 95460

  More actions

  October 29, 2010 at 9:48 am

  #1244296

  SELECT Field1, Field2 = count(*)

  FROM YourTable

  GROUP BY Field1

  ORDER BY Field1

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• jjtetzlo

  SSCrazy

  Points: 2511

  More actions

  October 29, 2010 at 9:53 am

  #1244299

  You're looking for the COUNT function (http://msdn.microsoft.com/en-us/library/aa258232(v=SQL.80).aspx).

  An example:

  declare @Counts table (field1 varchar(10), field2 char(1))

  insert into @Counts

  select 'jim', 'a' union all

  select 'jim', 'b' union all

  select 'jim', 'c' union all

  select 'john', 'a' union all

  select 'roger', 'a'

  select

  field1,

  count(*) as field2

  from @Counts

  group by field1

  order by count(*) desc

  - Jeff

• mick burden

  SSCarpal Tunnel

  Points: 4432

  More actions

  October 29, 2010 at 9:56 am

  #1244302

  That's great, works a treat,thanks Wayne and Jeff