SQL error 42803: I shouldn't be getting this error, what's going on?

Question

This is my first post here on stack overflow. Every other time I've needed something from here, someone else had already asked the question. I couldn't find any questions quite like this, so I though I'd make a new post.

I'm having a bit of an issue with a SQL query, and the error code doesn't make much sense to me. The query is as follows:

SELECT customer.account as "Account", customer.name as "Customer Name", sum(slpayment.payment_amount) as "Payment Amount", slpayment.reference, max(date(slpayment.payment_date)) as "Payment Date",  
CASE WHEN customer.userchk1 = '1' then 'B' else ' Y' END as type  
FROM customer  
INNER JOIN slpayment ON customer.company = slpayment.company and  
customer.account = slpayment.account  
WHERE slpayment.sales_period='11'

This query is flagging up SQL error 42803: "ERROR: column "customer.account" must appear in the GROUP BY clause or be used in an aggregate function"

I am familiar with this error and have managed to resolve it on other queries. However, what I fail to understand is why I'm getting this error. The query in question does not have a GROUP BY clause anywhere in the function. I had a look at other people who asked similar questions here on stack overflow, but all their queries did include a GROUP BY, and therefore it made sense why they were getting the error. I'm just at a loss as to why I'm having trouble here.

Thanks in advance for having a look!

Answer 1

You need to add group by clause and apply all columns except aggregated columns

SELECT customer.account as "Account", customer.name as "Customer Name", sum(slpayment.payment_amount) as "Payment Amount", slpayment.reference, max(date(slpayment.payment_date)) as "Payment Date", CASE WHEN customer.userchk1 = '1' then 'B' else ' Y' END as type 
FROM customer INNER JOIN slpayment 
ON customer.company = slpayment.company and customer.account = slpayment.account 
WHERE slpayment.sales_period='11'
group by customer.account , customer.name ,slpayment.reference,customer.userchk1