trying to sum a column in postgres but trying to limit the results first?

I am trying to get data for the last 5 years within my database, then sum the results. Here’s what I’ve been trying:

select SUM(cb.carbon) as totalCarbon
from company.carbon cb
where upper(ticker) = 'IBM.US' 
order by year desc
limit 5

but I’m getting this error:

Error occurred during SQL query execution

Reason:
SQL Error [42803]: ERROR: column "cb.year" must appear in the GROUP BY clause or be used in an aggregate function
  Position: 102

when I comment out order by year desc, then the query works but I need it to start at the most recent year. also the limit doesn’t seem to do anything when the order by year is missing.

Is there a way I can do this?

Answer

move the limit to a subquery. The subquery only returns the first 5 tuples. Then continue your processing as normal.

Example:

rip=# select * from r order by a desc ;
 a  
----
 10
  5
  4
  3
  1
(5 rows)

rip=# with T as 
         (select * from r 
          order by a desc 
          limit 3) 
       select sum(a) from T;
 sum 
-----
  19
(1 row)

So this is probably the answer to your query:

WITH T as (
  select carbon
  from company.carbon cb
  where upper(ticker) = 'IBM.US' 
  order by year desc
 limit 5
) 
select SUM(carbon) as totalcarbon from T