nested query: avg, count, group by

[gap]

hi,
I got a table as an example for my problem:

theme /subtopic /partners

a / fish / cornershop
a / fish / supermarket
a / vegetables / supermarket
b / vegetables / cornershop
b / fruits / cornershop
c / fruits / cornershop
c / fruits / cornershop
c / fish / supermarket



First, I want to count the number of partners fro each subtopic. Second, I want to get the Average Number of partners per subtopic for each theme.

I started with this sql statement, but its not working:

SELECT theme, subtopic, Avg(t.NumberofPartners) AS Percentage_Partners
FROM (SELECT COUNT(partners) As NumberofPartners
FROM my_table
GROUP BY subtopic)  AS t
GROUP BY theme;

Any Ideas?

[apr pillai]

Try the following SQL:

SELECT my_Table.theme, 
my_Table.subtopic, 
      Count(my_Table.subtopic) AS CountOfsubtopic,
      (SELECT count([theme]) as countoftheme from my_Table as myTable
 WHERE (my_Table.theme = myTable.theme) GROUP BY theme order by theme;) AS themecount, [countofsubtopic]/[themecount] AS [avg]
FROM my_Table
GROUP BY my_Table.theme, my_Table.subtopic;

Ref. Link: Sub-Query in Query Column Expressions

[gap]

thank you,
right direction! but I want to get for each theme an average for the number of partners in the each subtopic.

at least the table should look like:

theme / average number of partners per subtopic

a / [number]
b
c