I am working on a report that is requesting to count the total number of errors per element in the total row of the report for a given County. When I use the COUNT function it is taking the max value from the number of reads complete column for that district instead of actually tallying the errors in the column.

When I use the MAX function it is just placing a 1 there but not actually adding the errors either. Also when using the SUM function, it gives me the same values as the COUNT function.

Here is what I have:

UPDATE t
SET Element100Total = t2.ele100
FROM @Totals t
INNER JOIN (SELECT County, SUM(Element100) AS ele100 FROM @data GROUP BY County)t2 ON t2.County = t.County

I have also attached two screenshots to show you the results I'm getting. Any idea how I can get this row to display the sum of the errors?

wityh_MAX.PNG
with_SUM.PNG
with_SUM_2.PNG