Saturday, April 17, 2010

Writing a count distinct query in MS Access

I had to do this recently in my new job, and was annoyed to find that Access doesn't support the code
select columnA, count (distinct columnB) from table group by columnA

I did a bit of googling around, but wasn't too happy with the workarounds given. Some discussion, with possible solutions are given here:
discussion on stackoverflow
Well to cut a long story short, I devised my own workaround:
select Job,sum(pp) as number_distinct_fruits


(select Job, Fruit, 1 as pp

from Jobtable group by Job, Fruit) t

group by Job

This was based on data like the following
JobA Apple

JobA Apple

JobB Apple

JobB Pear

JobB Plum

JobC Apple

JobC Plum

JobC Plum

JobD Plum

JobD Banana

JobD Plum

JobD Cherry

The results came out as expected:

Job Number_distinct_fruits
==== ======================

JobA 1

JobB 3

JobC 2

JobD 3

This worked ok on my test data, but there are two things to watch out for. Firstly, the code will produce a grouped record for nul/blank entries in the Fruit field. If you don't want this as part of your count, insert a Where clause (Where Fruit is not null) in the inner select. I also found that the Access editor kept chamging the parenthesis for square brackets, so you may have to keep editing this when opening the query. Even so, it's quite an effective workaround I feel.

No comments:

Post a Comment