Re: Column "RPT_SUMMARY_STAT.ID" must be in the GROUP BY list

[Noel Grandin]

can you create a self-contained test case?

On 2012-11-01 18:39, Ciaran wrote:
> Hi all,
>
> I've having trouble running the following query on H2:
>
> SELECT * from table1 GROUP BY t
>
> It returns with the error:
> Column "TABLE1.ID" must be in the GROUP BY list; SQL statement:
> SELECT * from table1 GROUP BY t [90016-166] 90016/90016 (Help)
>
> Stranger still another table, table2, that's virtually identical to
> table1, doesn't exhibit this problem.
>
> I read the HELP on error 90016 and search this group but I'm no closed
> to a solution.
>
> Any pointers much appreciated.
>
> Thanks,
> ct
> --
> You received this message because you are subscribed to the Google
> Groups "H2 Database" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/h2-database/-/xmnXtx37rpIJ.
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to
> h2-database...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/h2-database?hl=en.

[Ciaran]

Hi Noel,

Thanks for the reply. Here's a short bit of SQL that shows the problem. The SELECT query at the end fails with:

Column "EX_DATA.ID" must be in the GROUP BY list; SQL statement:

SELECT * FROM ex_data GROUP by date [90016-166] 90016/90016 (Help)

I'm using 1.3.166, but have also tried 1.3.169 to no avail.

Would be great if you could shed some light on what's going on.

Cheers,

ct

DROP TABLE IF EXISTS ex_users;

CREATE TABLE ex_users (id BIGINT(19) NOT NULL,

name CHAR(20) NOT NULL,

PRIMARY KEY(id)

);

INSERT INTO ex_users(id, name) VALUES (1, 'Bill Hicks');

INSERT INTO ex_users(id, name) VALUES (2, 'PJ Harvey');

DROP TABLE IF EXISTS ex_data;

CREATE TABLE ex_data (id BIGINT(19) NOT NULL,

user_id BIGINT(19),

date TIMESTAMP(23, 10) NOT NULL,

t BIGINT(19) NOT NULL, PRIMARY KEY(id),

CONSTRAINT fk_user_id FOREIGN KEY(user_id) REFERENCES ex_users(id)

);

INSERT INTO ex_data(id, user_id, date, t) VALUES (1, 1, '2012-01-01', 10);

INSERT INTO ex_data(id, user_id, date, t) VALUES (2, 1, '2012-01-02', 20);

INSERT INTO ex_data(id, user_id, date, t) VALUES (3, 1, '2012-01-03', 30);

INSERT INTO ex_data(id, user_id, date, t) VALUES (4, 1, '2012-01-04', 40);

INSERT INTO ex_data(id, user_id, date, t) VALUES (5, 2, '2012-01-01', 11);

INSERT INTO ex_data(id, user_id, date, t) VALUES (6, 2, '2012-01-02', 21);

INSERT INTO ex_data(id, user_id, date, t) VALUES (7, 2, '2012-01-03', 31);

INSERT INTO ex_data(id, user_id, date, t) VALUES (8, 2, '2012-01-04', 41);

SELECT * FROM ex_data GROUP by date

[Thomas Mueller]

Hi,

This is normal for a relational database, your test case will not work with most databases (PostgreSQL for example, and most others). It might work with MySQL because this database is a bit weird in this respect. For example the following test case will give a similar error message for most databases:

drop table if exists test;

create table test(id int, name varchar(255));

insert into test values(1, 'hello');

insert into test values(1, 'world');

select * from test group by id;

Regards,

Thomas

On Fri, Nov 2, 2012 at 8:45 AM, Noel Grandin <noelg...@gmail.com> wrote:

can you create a self-contained test case?

On 2012-11-01 18:39, Ciaran wrote:

Hi all,

I've having trouble running the following query on H2:

SELECT * from table1 GROUP BY t

It returns with the error:
Column "TABLE1.ID" must be in the GROUP BY list; SQL statement:
SELECT * from table1 GROUP BY t [90016-166] 90016/90016 (Help)

Stranger still another table, table2, that's virtually identical to table1, doesn't exhibit this problem.

I read the HELP on error 90016 and search this group but I'm no closed to a solution.

Any pointers much appreciated.

Thanks,
ct
--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/xmnXtx37rpIJ.
To post to this group, send email to h2-da...@googlegroups.com.

To unsubscribe from this group, send email to h2-database+unsubscribe@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To post to this group, send email to h2-da...@googlegroups.com.

To unsubscribe from this group, send email to h2-database+unsubscribe@googlegroups.com.

[Ciaran Treanor]

Hi Thomas,

Yeah, it works on MySQL and it fails on our H2 unit testing database. Looking at the query now, I don't know how it would ever have worked! I'm going to have to do something like "SELECT date, sum(t) FROM ex_data GROUP BY date"

Thanks for responding.

ct