sql query with count

• John N

  SSC Veteran

  Points: 245

  More actions

  August 21, 2007 at 12:50 pm

  #121251

  I apoligize but i forgot i need to qeury 2 tables

  table 1

  jobid  jobname               state

  ______________________________

  1      accountant            ga

  2      sales                     fl

  3      clerk                     ga

  4      repairman               tn

  table 2

  statename             stateabbrv

  ______________________________

  Georgia                      ga

  Florida                        fl

  Tennesse                    tn

   

  I would like to bind data to dropdownlist

  Georgia(2)

  Florida(1)

  Tennesse(1)

  where (#) is the total count in that table. sorry Adrienne ..

  Thanks agian

   

• Adrienne250

  Right there with Babe

  Points: 737

  More actions

  August 21, 2007 at 12:57 pm

  #728409

  select state + '(' + cast(count(state) as varchar(2)) + ')'

  from theTable

  group by state

  order by count(state) desc, state

• John N

  SSC Veteran

  Points: 245

  More actions

  August 21, 2007 at 1:43 pm

  #728416

  i think i figured it out..

  select distinct table2.statename, table2.stateabbrv, count(table1.state) as c from

  table1 join table2 on table2.stateabbrv = table1.state

  group by table2.statename, table2.stateabbrv