CODEWITHSUNDEEP
sqlpostgresql
Snow
Snow

Reputation: 1138

Why do I have to use an aggregate function on this column?

I have a table that looks like this:

+---------+---------------+------------+-------+
| country | company       | date       | sales |
+---------+---------------+------------+-------+
| US      | Amazon        | 2019-11-01 | 100   |  
| US      | Amazon        | 2019-12-01 | 120   |   
| US      | Apple         | 2019-11-01 | 100   | 
| US      | Apple         | 2019-12-01 | 150   | 
| DE      | Amazon        | 2019-11-01 | 200   | 
| DE      | Amazon        | 2019-12-01 | 300   |
+---------+---------------+------+-----+-------+

I want to calculate the monthly growth rate of the sales in percentage for each of the companies, for each country. This is my SQL code:

select date_trunc('month', date),
        country,
        sum(sales),
        round(1.0 - sales::numeric/nullif(lag(sales::numeric, 1) over (order by date),0) *100,1) prev
from table 
group by date, country
order by date asc;

When I run the code, this error appears:

column "table.sales" must appear in the GROUP BY clause or be used in an aggregate function

How can I resolve this error?

Upvotes: 0

Views: 97

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270583

Because you need to lag the SUM of the sales, not the column itself:

select date_trunc('month', date), country, sum(sales),
       round(1.0 - sum(sales)::numeric * 100.0 /
             nullif(lag(sum(sales)::numeric, 1) 
       over (order by date), 0), 1) prev
from table 
group by date, country
order by date asc;

Upvotes: 2

forpas
forpas

Reputation: 164154

You must use sum(sales) instead of sales when aggregating, also partition by country in over and group by date_trunc('month', date):

select date_trunc('month', date),
        country,
        sum(sales),
        round(sum(sales)::numeric/nullif(lag(sum(sales)::numeric, 1) over (partition by country order by date_trunc('month', date)),0) *100 - 100,1) prev
from tablename 
group by date_trunc('month', date), country
order by date_trunc('month', date) asc;

See the demo.

Upvotes: 1

Related Questions