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:
x
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?
Advertisement
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