Count Distinct...

• Ritchy Henson

  SSC-Addicted

  Points: 461

  More actions

  July 5, 2006 at 5:28 pm

  #168829

  Hi - I have a basic SQL question, if anyone can help, I would really appreciate it!

  I've got a table with a primary key that is an incremental field and there is an accountID and one of the many fields is a coded lookup of statusID.  An account can have many entries and I want to count the number of records of each status type for a given account.

  I've tried

  select count (distinct CurrentStatusID) from myTable where AccountID = 2 - but this just tells me how many unique statuss exist - not how many of each.

  My sample data is:

  primarykey   acctid   statusid

  1                   2       1

  2                   2       1

  3                   2       2

  4                   2       3

   

  I'm wanting a query that results in:

  statusid     count

  1                2

  2                1

  3                1

   

  Any help is appreciated.  Thanks so much.

• Dinakar Nethi-176633

  SSCarpal Tunnel

  Points: 4689

  More actions

  July 5, 2006 at 5:35 pm

  #647655

  something like this?

  DECLARE @t table (primarykey int, acctid int, statusid int)

  insert into @t values (1, 2, 1)

  insert into @t values (2, 2, 1)

  insert into @t values (3,2, 2)

  insert into @t values (4, 2, 3)

  select statusid, count(*) from @t

  group by statusid

  ******************
  Dinakar Nethi
  Life is short. Enjoy it.
  ******************