Why distinct count subtotals don’t add up to the distinct count grand totals

Friday 21 October 2011 @ 9:39 pm

I had two different customers in the past week ask me why their distinct count grand totals were not totaling correctly. They had distinct count subtotals at the group level and distinct count grand totals at the report level.  But when they added up all the subtotals the sum didn’t match the grand total.  Usually it won’t because of what a distinct count is designed to do.

A distinct count summary will count how many different values there are in a column.   So if I group patient visits by doctor and then want CR to tell me how many different patients each doctor saw in that period, I would use a distinct count at the doctor group level.   And if I also wanted to know how many different patients were seen across ALL doctors I could create another distinct count of the patients and put it in the report footer.  But it is very likely that this grand total is not going to match the sum of the doctor subtotals.  This is because any patient who saw more than one doctor in the period will show up once in each doctor’s group, but then will only be counted only once in the grand total.

If you ever have a requirement where you need the grand total to be the sum of the subtotals you can purchase Expert Techniques volume 1 and read tip 25.

Leave a Reply

Recrystallize Pro