Selecting the MODE of a group of data in Microsoft Access
December 13, 2006 11:04 PM   Subscribe

Microsoft Access. An SQL "GROUP BY" query. How do I generate the mode of the values that it's grouping, when I want averages in other multiple fields?

I'm trying to query an Access database, grouping by a field, and I need a variety of operations performed on the other fields that it's grouping. Some of them (numerical data) I need an average, which is easy. Other fields (categorical data) I need it to give me the mode, or most common value, because an average doesn't make sense.

Searching for an answer, I've found a way to do it using a:

SELECT TOP 1 data, COUNT(*) Count
FROM [table]

query, but this only seems to work if you're working on a single field, not multiple fields that need different things done to them. What's the simplest way of doing this, short of writing a query for every field separately, then joining all the output tables back together?
posted by Jimbob to Computers & Internet (3 answers total)
The example you gave is the simplest way to do it in straight SQL. SQL has some noticeable shortcomings when it comes to statistics.

You can probably simplify the query's appearance with a function to calculate the mode of a field, and use separate subqueries for the averages and modes. But the database will still perform the same amount of work.

Something like:
SELECT T1.*, T2.*
  (SELECT col1, AVG(col2) as col2Average, AVG(col3) as col3Average
   FROM [table1]
   GROUP BY col1) as T1,
  (SELECT fn_Mode('table1', 'col4') as col4Mode, fn_Mode('table1', 'col5') as col5Mode
   FROM [table]) as T2

Where the fn_Mode() function just implements the example query you gave.

(Note: I haven't coded in Access for a few years, so the subqueries there might not work as written. You might have to create separate views for each ... but hopefully you get the idea.)
posted by ParsonWreck at 4:28 AM on December 14, 2006

(That FROM [table] in T2 shouldn't be there.)
posted by ParsonWreck at 4:44 AM on December 14, 2006

If your data only comes from one table, you should consider exporting it to Excel, and doing your statistical analysis there. Excel offers the MODE function that you seek.
posted by owhydididoit at 2:47 PM on December 14, 2006

« Older The life of a scholar for me?   |   firefox display problem Newer »
This thread is closed to new comments.