SQLPad Forum question: #5

This is my SQL query:

SELECT EXTRACT(YEAR from payment_ts) as year,
EXTRACT(MONTH from payment_ts) as mon, sum(amount) as rev
FROM payment
group by EXTRACT(MONTH from payment_ts);

Error:

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

I understand the error says that GROUP BY clause must have payment_ts, and I did run query without extracting the month, but of course results were incorrect.

Can someone please comment on what's wrong with this?

Answers

Comment

The error message meant your first column is not in an aggregate function, since you are aggregating on the second column only, the rest of the columns must be wrapped inside of an aggregate function.

So you need to group by both the first column and the second column.

That worked, thank you!

aashvi, March 25, 2022, 10:09 a.m.

Leon (949)

March 25, 2022, 9:46 a.m.