CODEWITHSUNDEEP
mysqldateaverage
Dembele
Dembele

Reputation: 1033

Find the average salary several months back - MySQL

I want to find the average salary of the staffs who started work within 8 months ago.

I tried making the code but somehow it's displaying NULL..

SELECT AVG(salary) FROM Staff  
WHERE salary = DATE_SUB(sWDate, INTERVAL 8 MONTH);

Everything is within this table.

Can anyone help me out please? I'm not sure what I've done wrong. Thanks.

Upvotes: 1

Views: 1209

Answers (2)

bonCodigo
bonCodigo

Reputation: 14361

Why salary = DATE_SUB(sWDate, INTERVAL 8 MONTH)?

WITHIN 8 MONTHS:

SELECT AVG(salary) FROM Staff  
WHERE sWDate <= NOW() AND sWDate >= DATE_SUB(Now(), INTERVAL 8 MONTH);

Another:

SELECT AVG(salary) FROM Staff  
WHERE sWDate <= NOW() AND sWDate >= DATE_ADD(Now(), INTERVAL -8 MONTH);

PS: It's best that you format Now() according to the date format you have in sWDate to get precise results. Otherwise you can use Year/Month.

e.g.:

SELECT AVG(salary) FROM Staff  
WHERE Date_Format(sWDate,'%Y-%m') <= Date_Format(NOW(),'%Y-%m') 
AND Date_Format(sWDate,'%Y-%m') >= Date_Format(DATE_ADD(Now(), INTERVAL -8 MONTH), '%Y-%m';

Upvotes: 1

rs.
rs.

Reputation: 27427

i think you should check Date field and not Salary for date

SELECT AVG(salary) FROM Staff  
WHERE [DateField] = DATE_SUB(NOW(), INTERVAL 8 MONTH);

Upvotes: 0

Related Questions