Column must appear in the GROUP BY

Moderator: NorbertKrupa

amine
Newbie
Newbie
Posts: 13
Joined: Tue Jul 16, 2013 3:18 pm

Column must appear in the GROUP BY

Post by amine » Mon Aug 26, 2013 5:48 pm

Hi,

I have been using Vertica for the past couple of months, and I am running into an issue that only occurs from the app, but not from the shell (I am suspecting something with the driver maybe?).
I am using vertica jdk5-6.1.2


java.lang.RuntimeException: Exception while executing statement : [Vertica][VJDBC](2640) ERROR: Column "name" must appear in the GROUP BY clause or be used in an aggregate function
errorCode: 2640, sqlState: 42803

Select
SUBSTRING(name,1,20) as g0,
subject as g1,
count(*) as c0,
avg(score) as c1,
From
my_table
Group By
SUBSTRING(name,1,20),
subject
Having
(count(*) > 5);

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Column must appear in the GROUP BY

Post by JimKnicely » Mon Aug 26, 2013 6:08 pm

Maybe try using the column aliases?
  • Select
    SUBSTRING(name,1,20) as g0,
    subject as g1,
    count(*) as c0,
    avg(score) as c1
    From
    my_table
    Group By
    g0,
    g1
    Having
    (count(*) > 5);
Or you can list the column positions in the GROUP BY clause:
  • Select
    SUBSTRING(name,1,20) as g0,
    subject as g1,
    count(*) as c0,
    avg(score) as c1,
    From
    my_table
    Group By
    1,
    2

    Having
    (count(*) > 5);
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

amine
Newbie
Newbie
Posts: 13
Joined: Tue Jul 16, 2013 3:18 pm

Re: Column must appear in the GROUP BY

Post by amine » Mon Aug 26, 2013 6:23 pm

It didn't help.
But I did some research, and I found that in postgres, the doc says:
When GROUP BY is present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions, since there would be more than one possible value to return for an ungrouped column.
And I am wondering if the the Vertica driver is following the same thought.
Again, this query works fine from the shell, it's just failing through the driver

Link:
http://www.postgresql.org/docs/current/ ... QL-GROUPBY

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Column must appear in the GROUP BY

Post by JimKnicely » Mon Aug 26, 2013 9:37 pm

Hmm.

What about this?

SELECT g0, g1, count(*) as c0, avg(score) as c1
FROM
(Select
SUBSTRING(name,1,20) as g0,
subject as g1,
score
From my_table
) foo
Group By
g0, g1
Having
(count(*) > 5);
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

amine
Newbie
Newbie
Posts: 13
Joined: Tue Jul 16, 2013 3:18 pm

Re: Column must appear in the GROUP BY

Post by amine » Tue Aug 27, 2013 4:10 pm

I unfortunately can't change the query structure (due to internal dependencies/reasons).
But I would like to know if this confirms that there's an issue with the driver or not. I can't see any reason why this would work from the shell, but not from the provided driver...
And if so, is there an ETA to fix this?

amine
Newbie
Newbie
Posts: 13
Joined: Tue Jul 16, 2013 3:18 pm

Re: Column must appear in the GROUP BY

Post by amine » Tue Aug 27, 2013 4:27 pm

Also, trying the same query without the substring section works fine both sides.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Column must appear in the GROUP BY

Post by JimKnicely » Tue Aug 27, 2013 4:49 pm

Hmm. I created a table name my_table with the following attributes and data:

Code: Select all

dbadmin=> \d my_table;
                                      List of Fields by Tables
 Schema |  Table   | Column  |     Type     | Size | Default | Not Null | Primary Key | Foreign Key 
--------+----------+---------+--------------+------+---------+----------+-------------+-------------
 public | my_table | name    | varchar(100) |  100 |         | f        | f           | 
 public | my_table | subject | varchar(100) |  100 |         | f        | f           | 
 public | my_table | score   | int          |    8 |         | f        | f           | 
(3 rows)

dbadmin=> SELECT * FROM my_table;
 name | subject | score 
------+---------+-------
 jim  | english |    96
 jim  | english |    97
 jim  | english |   100
 jim  | english |   100
 jim  | english |   100
 jim  | english |    95
(6 rows)
Next, I ran your query in SQuirreLSQL which is using the Vertica JDBC Driver. It ran without error:
SQuirrelQuery.png
SQuirrelQuery.png (74.28 KiB) Viewed 25664 times
Maybe it's not a JDBC Driver issue? What version of Vertica are you running?
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “JDBC”