Counting multi-assign terms

Take care when counting multi-assign terms to ensure you capture all assignments.

Incorrect

This incorrect example only counts a single arbitrary term assignment per record scanned:

RETURN TermCounts AS SELECT
    COUNTDISTINCT(Term) as NumTerms, /* wrong; term is de-multi-assigned 
    * prior to COUNTDISTINCT */
    COUNT(Term) as NumAssignments
GROUP BY Category

Correct

This correct example uses a SUM of COUNTs pattern. This pattern can be used any time where it is useful to first produce partial COUNTs and then add them up to get the total COUNT.

DEFINE Terms AS SELECT
    COUNT(1) AS Assignments
GROUP BY Term, Category ;

RETURN TermCounts AS SELECT
    COUNTDISTINCT(Term) as NumTerms,
    SUM(Assignments) AS NumAssignments
FROM Terms
GROUP BY Category