在 MySQL 中工作的 SQL 语句在 Postgresql 中不起作用 - Sum &group_by rails 3 [英] SQL statement working in MySQL not working in Postgresql - Sum & group_by rails 3

问题描述

所以我对一般的 SQL 工作还很陌生，更不用说 Rails，但我有这个适用于 MySQL 的语句:

So I'm very new to working in SQL in general, let alone rails but I have this statement that works in MySQL:

portfolio_values.select("portfolio_values.*, SUM(portfolio_values.value) as totals").group("portfolio_values.day").map(&:totals)

但是在 postgresql 中会抛出这个错误:

But in postgresql is throws this error:

GError:错误:portfolio_values.id"列必须出现在 GROUP 中BY 子句或在聚合函数中使用:SELECT投资组合值.*，SUM(portfolio_values.value) 作为总计 FROM"portfolio_values" WHERE "portfolio_values"."user_id" = 3 GROUP BY投资组合值.day ActiveRecord::StatementInvalid: PGError: 错误:列portfolio_values.id"必须出现在 GROUP BY 子句中或者是在聚合函数中使用

GError: ERROR: column "portfolio_values.id" must appear in the GROUP BY clause or be used in an aggregate function : SELECT portfolio_values.*, SUM(portfolio_values.value) as totals FROM "portfolio_values" WHERE "portfolio_values"."user_id" = 3 GROUP BY portfolio_values.day ActiveRecord::StatementInvalid: PGError: ERROR: column "portfolio_values.id" must appear in the GROUP BY clause or be used in an aggregate function

我只是不明白它说我应该做些什么不同的事情?

I just don't really understand what its saying I should be doing differently?

推荐答案

您只需将 portfolio_values.id 列添加到 GROUP BY 语句 - 如下所示:

You just have to add the portfolio_values.id column to the GROUP BY statement - like so:

portfolio_values.select("portfolio_values.*, SUM(portfolio_values.value) as totals").group("portfolio_values.id, portfolio_values.day").map(&:totals)

编辑

虽然这是有效的 SQL，但它不是一个有用的查询 - 请参阅其他帖子.

EDIT

While this is valid SQL, it's not a useful query - see other post.

这篇关于在 MySQL 中工作的 SQL 语句在 Postgresql 中不起作用 - Sum &group_by rails 3的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！