CODEWITHSUNDEEP
mysqlsqlaggregate-functionsaverage
vitalii
vitalii

Reputation: 33

AVG functions problem

I have table with structure 

id
YearBorn
sex
livelen

I need to receive output result table of average livelength(livelen) for both sexes with struct

year, len1(female), len2(male)

My query

SELECT YearBorn , 
AVG(IF(sex='F', LiveLen, 0)) len1(female),
AVG(IF(sex='M', LiveLen, 0)) len2(male)
FROM persons p
GROUP BY YearBorn

but it is not working properly Average value that is returned is the result of dividing sum livlen of fem(male) to the total number of records livlen , but not exactly to the number of f or m*.* What can you say about it

Upvotes: 2

Views: 511

Answers (2)

Álvaro González
Álvaro González

Reputation: 146588

Replace 0 with NULL. Otherwise, the value counts too:

70 + 0 + 90
------------ = 50
     3

70 + 90
------- = 80
   2

Upvotes: 3

gbn
gbn

Reputation: 432531

Use your own AVG

SELECT
   YearBorn , 
   SUM(IF(sex='F', LiveLen, 0))/COUNT(IF(sex='F', LiveLen, 0)) len1(female),
   SUM(IF(sex='F', LiveLen, 0))/COUNT(IF(sex='F', LiveLen, 0)) len2(male)
FROM persons p
GROUP BY YearBorn

Or use NULL which is ignored in aggregates. 0 is a value

SELECT
   YearBorn , 
   AVG(CASE WHEN sex = 'F' THEN LiveLen ELSE NULL END) len1(female),
   AVG(CASE WHEN sex = 'M' THEN LiveLen ELSE NULL END) len2(male)
FROM persons p
GROUP BY YearBorn

CASE is more portable then inline IF too

Upvotes: 1

Related Questions