Incorrect SUM returned 1

[gatetec]

I am trying to get the SUM of c.Assonum, which should return 2,606, but if I plug in sum (c.Assonum) here it returns the SUM of 80,899. Obviously, it is adding a lot more than what is there. Please advise how to get the correct SUM of c.Assonum in this query.

select
sum (c.Assonum),
sum (CASE WHEN StatusColumn ='AcceptVac' THEN 1 ELSE 0 END ) AS CntAccV,
sum (CASE WHEN StatusColumn ='AcceptNoVac' THEN 1 ELSE 0 END ) AS CntAccNV,
sum (CASE WHEN StatusColumn ='DeclineVac' THEN 1 ELSE 0 END ) AS CntDecV,
sum (CASE WHEN StatusColumn ='DeclineNoVac' THEN 1 ELSE 0 END ) AS CntDecNV,
sum (CASE WHEN StatusColumn ='JustVac' THEN 1 ELSE 0 END ) AS CntJustV,
(sum (CASE WHEN StatusColumn ='AcceptVac' THEN 1 ELSE 0 END )+
sum (CASE WHEN StatusColumn ='DeclineVac' THEN 1 ELSE 0 END ) +
sum (CASE WHEN StatusColumn ='JustVac' THEN 1 ELSE 0 END )) as TotalV
from
(Select AssociateIdLog,
Case
When FluAccept > 0 And FluVaccine > 0 Then 'AcceptVac'
When FluAccept > 0 And FluVaccine = 0 Then 'AcceptNoVac'
When FluDecline > 0 And FluVaccine > 0 Then 'DeclineVac'
When FluDecline > 0 And FluVaccine = 0 Then 'DeclineNoVac'
when FluDecline = 0 And FluAccept = 0 And FluVaccine > 0 Then 'JustVac'
End StatusColumn
from (
Select AssociateIdLog,
Count(Case When VisitReason = 'Flu Accept' Then 1 End) As FluAccept,
Count(Case When VisitReason = 'Flu Vaccine' or VisitReason = 'Flu Vaccine Urgicare' Then 1 End) As FluVaccine,
Count(Case When VisitReason = 'Flu Decline' Then 1 End) As FluDecline
from DailyLog
Where VisitDate between '10/01/2008' and '12/31/2008'
Group By AssociateIdLog
) As AliasForCount
) as d
Inner Join AssociatePersonal a
On d.AssociateIDLog = a.AssociateID
Inner Join CountAssociate c
On c.DeptName = a.dept
where
c.DeptDate = convert(varchar, GETDATE(), 101)
and CONVERT(VARBINARY, c.Deptname) = CONVERT(VARBINARY, UPPER(c.Deptname))

 

[RiverGuy]

Usually problems like these stem from issues with your joins. If you have a transactional table joining to a second table, and the second table has lets say three matching rows based on your join condition, then you will get three rows returned from the transactional table. So when you sum those rows, the numbers will be inflated.

One thing that comes to mind is your CountAssociate table. Without knowing more about your schema and business logic--if CountAssociate is a list of associates, then you will probably have more than one associate per department. But you're joining on department.

In this example, if Bill, Mike and Mary are in Department 1, if you join their records based on just department, you're going to have combinations of Bill/Bill, Bill/Mike, Bill/Mary, Mike/Mike, Mike/Bill, Mike/Mary, etc.

 

[gatetec]

Thanks so muuch for the tip.
I think I got it.