Ian


I'm having a look at it, I suspect that you'll need sub selects.

Roy Lambert

Ian

I hope I understood what you were trying to do correctly.

You'll have to replace my column / table names with your own




select _fkProjects AS "Job Type" , count(_LastResult) as "Tota lUnits", count(distinct _LastResult) as "Unique Units",

(SELECT count(_LastResult) FROM Calls X  WHERE X._LastResult = Calls._LastResult AND X._fkProjects = Calls._fkProjects GROUP BY _LastResult RANGE 1 TO 1)
-
(SELECT count(DISTINCT _LastResult) FROM Calls X  WHERE X._LastResult = Calls._LastResult AND X._fkProjects = Calls._fkProjects GROUP BY _LastResult RANGE 1 TO 1) AS "Repeats"


from Calls
where (trim(BOTH ' ' from _LastResult) <> '')
group by _fkProjects, _LastResult;

The fundamental problem was that the GROUP BY wasn't extended far enough to isolate the counts you wanted and the results you got sort of looked right but weren't

Roy Lambert