Getting a count of how many times each value from a list of values is used

• Jason Miller-427226

  SSC Rookie

  Points: 46

  More actions

  January 7, 2008 at 9:52 am

  #202048

  I have a table which contains 2 columns and stores a list of values which are used (as a "lookup table") in a field in several different tables. Column A is the "ID" field which is stored in the other tables, and Column B is a "Description" field. I am looking for a quick way to determine how many times each value is used in each table that it is stored in, and I would prefer the results to show all the values and show 0 if it's not used.

  For example

  in tblListOfValues

  FieldID FieldDesc

  1 Banana

  2 Orange

  3 Apple

  4 Pear

  5 Peach

  My logic is this:

  For each row in tblListOfValues

  Count (number of times it's used) in

  tblMyOrders.FruitID

  The ideal output would be:

  Banana 5

  Orange 0

  Apple 7

  Pear 4

  Peach 0

  Thanks for your help!

  Jason

• toniupstny

  Hall of Fame

  Points: 3986

  More actions

  January 7, 2008 at 10:11 am

  #765434

  You would join the tables and then get the count. -- better example

  select f.fieldesc, count(o.fieldid)

  from fruits f

  left join orders o on f.fruitid = o.fruitid

  group by f.fieldesc

  declare @orders table (id int identity(1,1), fruitid int )

  declare @fruits table (id int identity(1,1), fruitid int, fruitdesc varchar(10))

  insert into @orders

  select 1 union all

  select 2 union all

  select 2 union all

  select 4 union all

  select 4 union all

  select 4 union all

  select 4

  insert into @fruits

  select 1,'ap' union all

  select 2, 'ba' union all

  select 3, 'or' union all

  select 4, 'ch'

  select f.fruitdesc, count(o.fruitid)

  from @fruits f

  left join @orders o on f.fruitid = o.fruitid

  group by f.fruitdesc