I can't seem to translate from SQL to postgresql correctly when using SELECT, why is this wrong-CodePudding

My code works fine in a SQL database but when trying to execute on postgresql, it starts throwing an error. I've tried adding "symbol" after GROUP BY and even "symbol.symbol"

SELECT symbol,name,price,total, SUM(shares) 
FROM symbol 
WHERE user_id=? 
GROUP BY name

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

CodePudding user response：

Every field that is not part of a domain (or aggregate) function in the select list needs to be in the group-by clause. In your case, that's symbol, name, price, and total.

Furthermore, name and total are terrible field names, as they are reserved words. In SQL Server, you would enclose those in square brackets. In PostgreSQL you would put them in double-quotes.

But really, you should not use those names.

CodePudding user response：

The GROUP BY clause should include symbol, name, price, total and sum(shares). Your select clause should also include name.

SELECT name,symbol,name,price,total, SUM(shares) 
FROM symbol 
WHERE user_id=? 
GROUP BY name,symbol,name,price,total

This should at the very least be a valid statement.