A function calculating average age in years in SQL [postgresql]

Question

I am trying to create a plpgsql function that calculates the average age (in years) of some persons with ids (integers) stored in another table.

The code is:

begin DROP TABLE if EXISTS dates; DROP TABLE if EXISTS tmp; DROP TABLE if EXISTS ages; CREATE TABLE ages (age integer); --(...) In these lines, I create and fill the table tmp. I did not include this code --since it's not very much related to my problem. Nevertheless, this table has only --one integer column CREATE TABLE dates AS (SELECT "dateofbirth" from person where "idPerson" in (select "bookedforpersonID" from personsofthistype)); UPDATE ages SET (age) = ((SELECT extract (year from age(dateofbirth)) from dates)); return (select avg(age) from age); end;

Of course, dateofbirth is of type date. My problem is that the table ages that is created does not contain anything and thus I cannot return the correct result (the average age of its column). The function's return type is integer (and "Returns set" is not selected in pgadmin).

I am using PostgreSQL 9.3.4, pgAdmin III version 1.18.1.

Thank you.

This read as horribly over complicated. Do you have a table of people with birthdates and simply want the average age? — Karl Kieninger
– Karl Kieninger, Commented Apr 22, 2014 at 18:36

Karl Kieninger · Accepted Answer · 2021-02-19 03:28:57Z

If you are looking for average age from a person table you can do it much simpler. Something like this for starters:

 SELECT AVG(AGE(dateofbirth)) FROM person

Collectives™ on Stack Overflow

A function calculating average age in years in SQL [postgresql]

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related