SQL average estimated wage

[Retnuh]

I have a access database with jan - dec headings with peoples pay for those months in it.

eg.


jan | feb | mar | apr | may | june |
-----------------------------------------------------
100 | 20 | 3 | 4 | 0 | 3 |

and what i want to do is get the Estimated average for jan-dec wage. so


if the above was in calculation format -

100+20+3+4+0+3+..... * 12 = Actual total



But I need the estimated total. So basically, I want it to only devide by the number of fields with values in them.

So, say it was only till June...


jan | feb | mar | apr | may | june |
-----------------------------------------------------
100 | 20 | 3 | 4 | 0 | 3 |

This would be

100+2+3+4+3 /5 *6 = estimated average

I know that SQL doesnt act as a programming language but i was wondering if there was a way to count the fields that dont have 0 in them and then use the count as the devider?

Anyone any ideas?

 

[ameoba]

If you had the data in the correct format (google://database normalization), this would be a far simpler task - something like having rows of (ID, month, wage). If it was normalized, this would be a simple application of functions like count(), sum(), average() etc. If it's possible to fix this, I'd do that before I tried working with it the way it is now.

If, OTOH, you're stuck with this mess, you're going to have a long, ugly query. I don't know what commands you have available in Access but in Oracle, I'd start out making use of the NVL() function.

NVL2(value, default_value, other_value) = other_value, unless value = null, then it returns default value.

A quick look at the Access documention it looks like you'd use something like

IIF(isnull(value),default_value,other_value)

instead.

So you'd end up with something like

select (jan + feb + ... dec) / ( iif(jan > 0,1,0) + iif(feb>0,1,0) + ... ) * (iif(isnull(jan),0,1)) + iif(isnull(feb),0,1) + ...) from ugly_table

 

[mikeblas]

jan | feb | mar | apr | may | june |
-----------------------------------------------------
100 | 20 | 3 | 4 | 0 | 3 |

and what i want to do is get the Estimated average for jan-dec wage. so


if the above was in calculation format -

100+20+3+4+0+3+..... * 12 = Actual total

Huh? First, you must mean

(100+20+3+4+0+3+.....) * 12 = Actual total

because you wouldn't multiply only December by 12. But why would you multiply the sum of all months income by 12?

But I need the estimated total. So basically, I want it to only devide by the number of fields with values in them.

100+2+3+4+3 /5 *6 = estimated average

I think you mean:

((100+2+3+4+3)/5) *6 = estimated average

I know that SQL doesnt act as a programming language but i was wondering if there was a way to count the fields that dont have 0 in them and then use the count as the devider?

Anyone any ideas?

I don't use Access; does it have an UNPIVOT operator? If so, it's not hard to get the numbers in a usable way.

Here's a complete example in Transact SQL

CREATE TABLE EmployeeMonthlyWages
(
   EmployeeID INT NOT NULL,
   January INT,
   February INT,
   March INT,
   April INT,
   May INT,
   June INT,
   July INT,
   August INT,
   September INT,
   October INT,
   November INT,
   December INT
)
go


INSERT INTO EmployeeMonthlyWages
(EmployeeID, January, February, March, April, May, June, July, August, September, October, November, December)
VALUES (30001, 100, 20, 3,  4, NULL, 3, NULL, NULL, NULL, NULL, NULL, NULL)
GO


SELECT EmployeeID, AVG(Salary)*12
FROM 
   (SELECT EmployeeID, January, February, March, April, May, June, July, August, September, October, November, December
   FROM EmployeeMonthlyWages) p
UNPIVOT
   (Salary FOR Month IN 
		(January, February, March, April, May, June, July, August, September, October, November, December)
)AS unpvt
GROUP BY EmployeeID
GO

which returns:

EmployeeID  
----------- -----------
30001       312

 

[Retnuh]

INSERT INTO 2005_Calc ( Emp_no, Salary_q1, Salary_q2, Salary_q3, Salary_q4, Annual_Salary, Car_q1, Car_q2, Car_q3, Car_q4, Annual_Car, est_Annual_Car, ni_q1, ni_q2, ni_q3, ni_q4, Annual_Ni, Est_Annual_Ni, Est_Gross_wage, Gross_wage )
SELECT [2005_Data].Emp_no, Feb_salary+Mar_salary+Apr_salary AS q1_salary, May_salary+June_salary+July_salary AS q2_salary, Aug_salary+Sep_salary+Oct_salary AS q3_salary, Nov_salary+Dec_salary+Jan_salary AS q4_salary, q1_salary+q2_salary+q3_salary+q4_salary AS annual_salary, Feb_Car+Mar_Car+Apr_Car AS q1_car, May_Car+June_Car+July_Car AS q2_car, Aug_Car+Sep_Car+Oct_Car AS q3_car, Nov_Car+Dec_Car+Jan_Car AS q4_car, q1_Car+q2_Car+q3_Car+q4_Car AS annual_car, 0 AS est_annual_car, Feb_Ni+Mar_Ni+Apr_Ni AS q1_ni, May_Ni+June_Ni+July_Ni AS q2_ni, Aug_Ni+Sep_Ni+Oct_Ni AS q3_ni, Nov_Ni+Dec_Ni+Jan_Ni AS q4_ni, q1_Ni+q2_Ni+q3_Ni+q4_Ni AS annual_ni, 0 AS est_annual_ni, annual_ent_salary+annual_car+annual_Ni, annual_salary+annual_car+annual_ni
FROM 2005_Data;



that is the current query im running. its the EST's im having a bit of bother with.

basically, i want to be able to work out how much (estimated average) someone will get in NI and Car allowance.

So if they only worked jan feb mar the rest of the days shouldnt be counted in the average.

so...

((1+1+1) / 3) *12 = est_average_X

is an example of them only being paid for the 3 months and how I would get the est average of how much they would recieve.

as far as im aware, its the counting part for the / that is causing the problem. I dont know how to get it to ignore anything with NULL or 0 in it.