current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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?

share|improve this question
    
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
add comment

3 Answers

up vote 0 down vote accepted

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.

share|improve this answer
    
This results in a legal but utterly pointless statement. –  Erwin Brandstetter Jun 27 '12 at 0:02
    
True that, I didn't really look at rest of the query. –  Matthew Lehner Jun 27 '12 at 0:52
add comment
up vote 1 down vote

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.

share|improve this answer
    
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 2: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 2: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 2: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 2:16
add comment
up vote 0 down vote

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;

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.