Sum fields in one table and count rows in another

Transact-SQL
1

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],
(SELECT SUM(nc.new_maximumattendees)
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

2

Would you please post your table definitions (CREATE TABLE statements) and some test data (INSERT INTO statements) and show what you expect from the query and what you are getting?

1 Like
3

Is [typecodename] the unique / primary key for the [filteredcampaign] table?

EXEC sp_help 'filteredcampaign'

should tell you what is available as a Primary Key / Unique index.

1 Like
4

I resolved this with the following script

SELECT DISTINCT(fc.typecodename),
SUM(fc.new_maximumattendees),
COUNT(fc.campaignid),
(SELECT COUNT(fcr.new_attendedname)
FROM FilteredCampaignResponse fcr
INNER JOIN filteredcampaign fc2 ON fcr.regardingobjectid = fc2.campaignid
WHERE fcr.new_attendedname = 'Yes' AND
fc2.typecodename = fc.typecodename AND
CONVERT(VARCHAR, fc2.actualstart, 112) >= '20140101' AND
fc2.new_eventbannername IN('XXX,'YYY) AND
fc2.new_publishedeventname IS NOT NULL AND
fc2.new_publishedeventname NOT LIKE '%Test%' AND
fc2.statuscodename = 'Completed')
FROM filteredcampaign fc
WHERE fc.statuscodename = 'Completed' AND
fc.new_eventbannername IN('XXX','YYY') 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'
GROUP BY fc.typecodename

5

Can't you just use

fc.actualstart >= '20140101'

?

Converting the date to a string, to make the comparison, is very inefficient and will mean that SQL won't use any index that is available for that column.

(Not recommended to use VARCHAR without a size parameter as sooner or later SQL's default sizes will catch you out)

fc.new_publishedeventname NOT LIKE '%Test%'

similarly inefficient, sadly. Also I think there is a high risk of it matching "test" in "normal" words - e.g. "brightest"

6

Thanks for the feedback Kristen, I'm back into SQL after 15 years of not touching it, so I'm very much out of practice and have no doubt forgotten a hell of a lot. This is a one off report which executes in about 4 seconds, so not too fussed about the inefficiency of the NOT LIKE '%Test%' clause, there are only 150 records to scan and only 2 events match the criteria which I want filtered out, but definitely take on board the date clause, thanks for that, that was a case of "How I used to do it in SQL Server 2000".

1 Like