CODEWITHSUNDEEP
postgresqlaverage
PangolinKing
PangolinKing

Reputation: 115

Using average in query (PostgreSQL)

I'm having trouble with coming up with a query to find the average age of the buses used for each bus company from the following schema:

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

route

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 NED's average bus age is 4246.666 = 4247 days assuming today is 2013-03-18.

What would the whole query look like?

Upvotes: 5

Views: 15151

Answers (2)

pilcrow
pilcrow

Reputation: 58534

I can't test 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

ought to Do What You Want (tm). Date subtraction in pg gives the difference in days.

Upvotes: 3

digitaljoel
digitaljoel

Reputation: 26574

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

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

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

If you want to deal with the interval instead of a number of days then you can do this instead

select avg(age(birthday)) from stuff;

Upvotes: 0

Related Questions