CODEWITHSUNDEEP
sqlpostgresql
java
java

Reputation: 1214

more than one AVG column with diffrent conditions

I have a table as follows:

id   year  value
1     2012   10
2     2013    7
3     2013    7
4     2014    8
5     2014    10
6     2015    6
7     2011    12

I need to write a query which gives the AVG value of the last 4 years from today. Meaning that if today is 2016 then the AVG is on 2015,2014,2013.

Basicly this could be done with 3 queries:

Select avg(value) as a
from tab
where year=2015

and

Select avg(value) as b
from tab
where year=2014

and

Select avg(value) as c
from tab
where year=2013

The results based on the given values should be:

2013   7
2014   9
2015   6

Since all of them is on the same table... How can I do that in one query (postgresql)? it should be without a WHERE.

Something like:

Select avg(with condition) as a, avg(with condition) as b, avg(with condition) as c
from tab

Upvotes: 1

Views: 64

Answers (2)

Shubham Batra
Shubham Batra

Reputation: 2375

select 
    avg(case when year = date_part('year', NOW()) then value end) as avg_2016,
    avg(case when year = ((date_part('year', NOW())) - 1 ) then value end) as avg_2015,
    avg(case when year = ((date_part('year', NOW())) - 2 ) then value end) as avg_2014,
     avg(case when year = ((date_part('year', NOW())) - 3 ) then value end) as avg_2013
from tab

Upvotes: 1

FuzzyTree
FuzzyTree

Reputation: 32392

You can group by year and constrict to the years you want in your where clause

select avg(value), year
from tab
where year in (2013,2014,2015)
group by year

The query above will give you 3 separate rows. If you prefer a single row then you can use conditional aggregation instead of a group by

select 
    avg(case when year = 2013 then value end) as avg_2013,
    avg(case when year = 2014 then value end) as avg_2014,
    avg(case when year = 2015 then value end) as avg_2015,
from tab
where year in (2013,2014,2015)

Upvotes: 3

Related Questions