views:

45

answers:

1

So lets say I have a table that looks something like this:

ItemName                ProductType
----------------------------------------------------------
Name1                   Type1
Name2                   Type1
Name3                   Type1
Name4                   Type2
Name5                   Type3

and so on for thousands of records. I want a sql statement to find the average number of Names per Types. I want to use it in vba, behind a form.

How would I go about this? I have the sneaking suspision that this is something simple that is escaping me right now.

EDIT: Sorry....a little unclear. I mean in a table like fashion, have the types listed out with a count for each of the relative Names

Averages
Type                Count of Names
-------------------------------------------
Type1                   5
Type2                   6

so basically see how many names applicable to types, what Inner Select statement should I use to accomplish this?

Thanks Justin

+1  A: 
 select avg( c ) from 
    ( select ProductType, count( ItemName ) c
    from myTable
    group by ProductType )
Randy
thanks very much....what if I wanted to list it out per ProductTypes? I mean show the individual average of ItemName(s) per ProductTypes
Justin
@Justin Please edit your question to show us your desired output.
HansUp
there is no average per type - you can run the inner select to see the count per type.
Randy
yes...you are right for sure. what I am looking for is not an average but a count for each name per relative type. apologies; thanks for your help.
Justin