I created this calculation to determine the total memberships that are active according to a user set start and end date. The calc lists all the membership renewals in a year with their corresponding expiry and creation dates. Because the start and end date are determined by the user member renewals can repeat, but I would only like to count the number of unique members. The calculation below does this part perfectly.
The second number I would like to calculate, which I'm having troubles with, is the sum associated with each distinct contact to determine the total amount associated which each active membership. The sql below only works if all the active memberships between the start and end date already happen to be distinct, but results incorrectly if a membership repeats.
Is there a way to determine the amount total in relation to the first column that is selected?
memberships= ExecuteSQL ("
SELECT COUNT (DISTINCT MContactIds), Sum(Amount)
FROM \"Line Items\"
WHERE MDateExpiry > EndDate
AND MDateCreation <= EndDate
" ;¶; ¶) ;