Hoping somebody can help me with this one, related to Dynamics CRM.
I'm trying to sum fields in one table and count the related rows in another table, the counting of rows works fine, but the summing of the field values is not working at all, I can't understand how the result is being calculated as the output result does not seem to correlate with any additions/multiples of values.
SELECT ISNULL(fc.typecodename, '') [Type],
FROM filteredcampaign nc
WHERE nc.typecodename = fc.typecodename) [Maximum Attendees],
COUNT(fcr.new_attendedname) [Actual Attendees]
FROM filteredcampaign fc
LEFT JOIN FilteredCampaignResponse fcr ON fc.campaignid = fcr.regardingobjectid
WHERE fc.statuscodename = 'Completed' AND
fc.new_eventbannername ='XXXX' AND
fc.typecodename NOT IN('XXX','YYY','ZZZ') AND
fc.new_publishedeventname IS NOT NULL AND
fc.new_publishedeventname NOT LIKE '%Test%' AND
CONVERT(VARCHAR, fc.actualstart, 112) >= '20140101' AND
fcr.new_attendedname = 'Yes'
GROUP BY fc.typecodename
ORDER BY fc.typecodename