Using average in query (PostgreSQL)

Question

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?

pilcrow · Accepted Answer · 2013-03-18 21:10:35Z

3

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.

edited Mar 18, 2013 at 21:10

answered Mar 18, 2013 at 20:29

pilcrow

59.2k14 gold badges101 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

pilcrow Over a year ago

@HalfFrench, how's that? Again, cannot run against a live pg right now.

PangolinKing Over a year ago

yeah that works. You've made such a simple query. I always over complicate mine and I still get them wrong. Cheers.

digitaljoel · Accepted Answer · 2013-03-18 20:51:40Z

0

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;

answered Mar 18, 2013 at 20:51

digitaljoel

26.6k15 gold badges94 silver badges116 bronze badges

2 Comments

pilcrow Over a year ago

"birthday" must be a timestamp type for your stuff; coerce it to a date type and you will work with integers rather than intervals.

digitaljoel Over a year ago

verified that if the column is of type date then the first statement does not need the extract statement. The second does still return an interval thanks to the age function. Thanks for pointing that out pilcrow. sqlfiddle.com/#!12/2f53c/2

Collectives™ on Stack Overflow

Using average in query (PostgreSQL)

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related