CODEWITHSUNDEEP
sqlms-accessvbaparameters
Justin
Justin

Reputation: 4539

Access 2003/2007 : Query to find average of one text field grouped by another in table

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

Upvotes: 0

Views: 1242

Answers (1)

Randy
Randy

Reputation: 16677

 select avg( c ) from 
    ( select ProductType, count( ItemName ) c
    from myTable
    group by ProductType )

Upvotes: 1

Related Questions