SQL statement working in MySQL not working in Postgresql - Sum & group_by rails 3

Question

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)

But in postgresql is throws this error:

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?

The answer directly depends on the version or PostgreSQL in use - which I fail to gather from your question. — Erwin Brandstetter, Jun 26 '12 at 23:40

Erwin Brandstetter · Answer 1 · 2012-06-27T02:19:44.637

1

Since PostgreSQL 9.1 a primary key column in the GROUP BY list covers the whole table. Therefore, given that portfolio_values.id is, in fact, the primary key, you can simplify:

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

I quote the 9.1 release notes:

Allow non-GROUP BY columns in the query target list when the primary key is specified in the GROUP BY clause (Peter Eisentraut)

The SQL standard allows this behavior, and because of the primary key, the result is unambiguous.

However, in your case, this results in nonsense either way.

a. portfolio_values.id is the primary key.

Then SUM(portfolio_values.value) is pointless. There can only be one row per group.

b. portfolio_values.id is not the primary key.

Then you cannot include portfolio_values.* in the SELECT list.

If you wish to learn the meaning of * in a SELECT, start by reading the manual here.

edited Jun 27 '12 at 02:19

answered Jun 26 '12 at 23:52

Erwin Brandstetter

605,456
145
1,078
1,228

I dont see how this could accomplish my goal, as its not grouping by day (which is a column in the table) – Elliot Jun 27 '12 at 02:00
@Elliot: The point is, your goal is impossible. Selecting `portfolio_values.*` and `SUM(portfolio_values.value` at the the same time is logical nonsense. You cannot have the pudding and eat it, too. – Erwin Brandstetter Jun 27 '12 at 02:11
I dont even know what portfolio_values.* does - I'm extremely new to SQL, I'm just trying to return the sum of values grouped by day. Which I accomplished like such: portfolio_values.select("portfolio_values.day, SUM(portfolio_values.value) as totals").group("portfolio_values.day") – Elliot Jun 27 '12 at 02:14
@Elliot: Please *edit your question* and describe your goal there. An answer will be found. :) That new example makes sense now. – Erwin Brandstetter Jun 27 '12 at 02:16

Matthew Lehner · Accepted Answer · 2012-06-27T01:16:13.580

0

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)

EDIT

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

edited Jun 27 '12 at 01:16

answered Jun 26 '12 at 23:05

Matthew Lehner

3,967
3
26
35

True that, I didn't really look at rest of the query. – Matthew Lehner Jun 27 '12 at 00:52

score 0 · Answer 3 · answered Jun 27 '12 at 15:45

0

You probably want distinct on:

http://www.postgresql.org/docs/9.1/static/sql-select.html#SQL-DISTINCT

Specifically something like:

select distinct on (yourdatefield) field1, field2, field3 from sometable;

answered Jun 27 '12 at 15:45

Scott Marlowe

8,490
3
23
21

SQL statement working in MySQL not working in Postgresql - Sum & group_by rails 3

3 Answers3

EDIT

Linked