Distinct Count Problem

112 Views

I have a problem where a distinct count of key field is coming back 1 higher than my test sql statement. I am trying to get a total number of records based on several criteria and every time it has one more than it should.

Here is one of my formulas

Here is TOTAL_PENDING_APPLICATION_SW

if {WR_MAIN.WATER_CODE}='S' and {WR_MAIN.RECORD_TYPE}='A' and {WR_MAIN.STATUS_CODE}='N' then {WR_MAIN.PERMIT_NUMBER} else ''

Here is SUM_TOTAL_PENDING_APPLICATION_SW

DistinctCount({@Total_Pending_Applications_SW})

This comes back with 161 and the SQL comes back with 160. The same can be said for other criteria and formulas.

So, I created a sql expression field in crystal that uses my SQL and it comes up with the correct value. I guess, I could use those instead of formulas, but that has to be a huge bug with formulas, or I am not doing something correct.

(Select Distinct(count(*)) from wr_main where water_code='S' and record_type='A' and status_code='N')

It almost seems like Crystal is starting with 1 instead of 0 when doing a DistinctCount