Sum of Sums

[jazminecat]

Hi all, I have the following query, and i need to sum the sums of three fields. Here's the SQL:

SELECT [Qry-2Empl].CostCntr, Count([Qry-2Empl].CostCntr) AS CountOfCostCntr, Sum([Qry-2Empl].GrossPay) AS G, Sum([Qry-2Empl].Long) AS L, Sum([Qry-2Empl].Prem_Amt) AS P
FROM [Qry-2Empl]
GROUP BY [Qry-2Empl].CostCntr
HAVING ((([Qry-2Empl].CostCntr)=520))
ORDER BY [Qry-2Empl].CostCntr;

What I really need is to sum G+L+P for each CostCntr -

The records are sorted by employee, so for the above example, CostCntr #520, there are three employees.

My data looks like this:

CostCntr G L P
520 38,232 900 0
520 60,446 660 0
520 76,440 0 7560

So for this cost center I need to make the query return the sum of all of the above numbers, of 184238 for CostCntr 520.

Any insight is much appreciated!

I've tried putting the sums in like this:

SELECT [Qry-2Empl].CostCntr, [Qry-2Empl].CostCntr, Sum(Sum([Qry-2Empl].GrossPay)+Sum([Qry-2Empl].Long)+Sum([Qry-2Empl].Prem_Amt)) AS Expr1
FROM [Qry-2Empl]
GROUP BY [Qry-2Empl].CostCntr, [Qry-2Empl].CostCntr
HAVING ((([Qry-2Empl].CostCntr)=520))
ORDER BY [Qry-2Empl].CostCntr;

but get an error, cannot have an aggregate function in expression.

Thanks in advance!

 

[lameid]

SELECT [Qry-2Empl].CostCntr, Sum(NZ([Qry-2Empl].GrossPay,0) + nz([Qry-2Empl].Long,0) + nz([Qry-2Empl].Prem_Amt,0)) AS Total
FROM [Qry-2Empl]
GROUP BY [Qry-2Empl].CostCntr
HAVING ((([Qry-2Empl].CostCntr)=520))
ORDER BY [Qry-2Empl].CostCntr;

 

[PHV]

Why not simply this ?
SELECT CostCntr, Sum([GrossPay])+Sum([Long])+Sum([Prem_Amt]) AS Expr1
FROM [Qry-2Empl]
WHERE CostCntr=520
GROUP BY CostCntr

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lameid]

PHV's Select is better (faster).

I used NZ because anything + Null is Null... SUM and the other aggregate functions ignore nulls so it's the same result. In your example, you don't have any nulls so it wouldn't have mattered.

 

[jazminecat]

There are more records than those I show, but the one that works is this:

Sum(NZ([Qry-2Empl].[GrossPay],0)+nz([Qry-2Empl].[Long],0)+nz([Qry-2Empl].[Prem_Amt],0))

from the code builder window, not the sql window.

PHV, i did try your code, and strangely, it doesn't return totals on a number of the records. the cstcntr=520 was a tester so I could see if it totalled that one correctly. Now i have taken that out, and can see that the above code returns a total for all cost centers, whereas your code doesn't. is it because it's ignoring the nulls perhaps?

thanks for your help guys. it got me pointed in the right direction and it works now.

 

[lameid]

I kind of wondered about that... I couldn't remember if all the values were null if it returned 0 or null. Apparently, it returns nulls. Therefore when you sum some of them, one of your fields contained all Nulls. That may be by design. It could also mean that you should update the values to 0's and make the default 0.

If Nulls belong and you want to sum despite them I think that something else would work a little faster...

nz(Sum([GrossPay]),0) + nz(Sum([Long]),0)+ Nz(Sum([Prem_Amt]),0)