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
– Erwin Brandstetter, Commented Jun 26, 2012 at 23:40

Erwin Brandstetter · Accepted Answer · 2012-06-27 02:19:44Z

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, 2012 at 2:19

answered Jun 26, 2012 at 23:52

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Elliot Over a year ago

I dont see how this could accomplish my goal, as its not grouping by day (which is a column in the table)

Erwin Brandstetter Over a year ago

@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.

Elliot Over a year ago

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")

Erwin Brandstetter Over a year ago

@Elliot: Please edit your question and describe your goal there. An answer will be found. :) That new example makes sense now.

Matthew Lehner · Accepted Answer · 2012-06-27 01:16:13Z

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, 2012 at 1:16

answered Jun 26, 2012 at 23:05

Matthew Lehner

4,0573 gold badges30 silver badges35 bronze badges

1 Comment

Matthew Lehner Over a year ago

True that, I didn't really look at rest of the query.

Scott Marlowe · Accepted Answer · 2012-06-27 15:45:14Z

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, 2012 at 15:45

Scott Marlowe

9,0403 gold badges27 silver badges21 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

4 Comments

EDIT

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

EDIT

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related