Using the average value in a query (PostgreSQL)

Question

I had trouble finding a query to find out the average age of the buses used for each bus company from the following diagram:

buscompany

company_id | name 1 NED 2 JIM 

bustype

 type_id | date_made 1 2006-01-26 2 1999-05-05 3 2000-09-01 

way

 route_id | bustype | bus_company 1 2 1 2 1 1 3 1 1 4 3 2 5 3 2 6 1 2 7 2 2 

In this example, the average age of the NED buses is 4246.666 = 4247 days, counting today 2013-03-18.

What will the whole query look like?

Answer 1

I can’t check right now, but something like:

 -- assuming "date_made" is a pg DATE column -- SELECT buscompany.name, ROUND(AVG(CURRENT_DATE - date_made)) FROM buscompany LEFT JOIN route ON route.bus_company = buscompany.company_id LEFT JOIN bustype ON route.bustype = bustype.type_id GROUP BY 1 

should do what you want (tm). Subtracting the date in pg gives the difference in days.

Answer 2

The following (based on the sample table that I made) gave me the average value in days.

 select extract(day from avg(current_date-birthday)) from stuff; 

avg gives you the interval, not the number of days, so I made an extract.

If you want to deal with the interval, and not after a few days, you can do this instead

 select avg(age(birthday)) from stuff;