473,850 Members | 2,189 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

field must appear in the GROUP BY clause or be used in an aggregatefuncti on?

Hey all.

I've hit an SQL problem that I'm a bit mystified by. I have two different
questions regarding this problem: why? and how do I work around it?

The following query:

SELECT GCP.id,
GCP.Name
FROM Gov_Capital_Pro ject GCP,
WHERE TLM.TLI_ID = $2
group by GCP.id
ORDER BY gcp.name;

Produces the following error:

ERROR: column "gcp.name" must appear in the GROUP BY clause or be used in an aggregate function

That field is a CHAR, so I'm not sure what kind of aggregate to use,
or (more important to my understanding) why one is necessary.

As I said, I'm not sure I understand why this occurs. I'm assuming that I
don't understand "group by" as well as I thought I did ;)

This isn't my query, I'm translating a system prototyped in MSSQL to
Postgres. This query _does_ work in MSSQL. Does that constitute a
bug in MSSQL, or a shortcomming of Postgres, or just a difference of
interpretation?

--
Bill Moran
Potential Technologies
http://www.potentialtech.com
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 22 '05 #1
12 25315
Bill Moran said:
I've hit an SQL problem that I'm a bit mystified by. I have two different
questions regarding this problem: why? and how do I work around it?

The following query:

SELECT GCP.id,
GCP.Name
FROM Gov_Capital_Pro ject GCP,
WHERE TLM.TLI_ID = $2
group by GCP.id
ORDER BY gcp.name;

Produces the following error:

ERROR: column "gcp.name" must appear in the GROUP BY clause or be used in
an aggregate function


Since you're not agregating data, can't you use a select distinct instead?

SELECT distinct GCP.id, GCP.Name
FROM Gov_Capital_Pro ject GCP, {?something missing here?}
WHERE TLM.TLI_ID = $2
ORDER BY gcp.name;

(BTW, I wasn't clear if the where clause trying to join to another table?)

Doesn't answer your original question, but hope it helps anyway.

John Sidney-Woollett
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

Nov 22 '05 #2
John Sidney-Woollett wrote:
Bill Moran said:
I've hit an SQL problem that I'm a bit mystified by. I have two different
questions regarding this problem: why? and how do I work around it?

The following query:

SELECT GCP.id,
GCP.Name
FROM Gov_Capital_Pro ject GCP,
WHERE TLM.TLI_ID = $2
group by GCP.id
ORDER BY gcp.name;

Produces the following error:

ERROR: column "gcp.name" must appear in the GROUP BY clause or be used in
an aggregate function
Since you're not agregating data, can't you use a select distinct instead?


Not sure. I'll have to get back to the programmer who wrote the orignal
SELECT and find out what kind of data he is actually trying to acquire.
SELECT distinct GCP.id, GCP.Name
FROM Gov_Capital_Pro ject GCP, {?something missing here?}
WHERE TLM.TLI_ID = $2
ORDER BY gcp.name;

(BTW, I wasn't clear if the where clause trying to join to another table?)
Yes, my bad. The actual query causing the problem is a bit longer with about
6 joins to it. I did test:

select id, name from gov_capital_pro ject group by id order by name;

and it causes the same error, so I thought I'd make the question simpler by
removing the parts that obviously weren't contributing to the problem.
Doesn't answer your original question, but hope it helps anyway.


It may, thanks for the input!

Like I said, the most important part (to me) is to understand why
Postgres refuses to run this. The fact that I don't know why points
to an obvious lack of understanding on my account, and I'd like to
remedy that :D

To that effect, if anyone can point me to a doc that will help me
gain a better understanding of why this error occurs, I'd be happy
to read it!

--
Bill Moran
Potential Technologies
http://www.potentialtech.com
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

Nov 22 '05 #3
Bill Moran wrote:
Hey all.

I've hit an SQL problem that I'm a bit mystified by. I have two different
questions regarding this problem: why? and how do I work around it?

The following query:

SELECT GCP.id,
GCP.Name
FROM Gov_Capital_Pro ject GCP,
WHERE TLM.TLI_ID = $2
group by GCP.id
ORDER BY gcp.name;

Produces the following error:

ERROR: column "gcp.name" must appear in the GROUP BY clause or be used
in an aggregate function
The reason the grouping requires either an attribute to be
aggregated or apart of the group by list is that if it were not, an
arbitrary value would have to be selected:

[test@lexus] select * from projects;
dept | project
-----------+--------------
Finance | Y2K
Corporate | Y2K
Corporate | Annual Audit
(3 rows)
[test@lexus] select dept, project from projects group by dept;
ERROR: column "projects.proje ct" must appear in the GROUP BY clause
or be used in an aggregate function

If this were to be permitted, which project should be selected,
'Y2K' or 'Annual Audit'?

[test@lexus] select dept, project from projects group by dept, project;
dept | project
-----------+--------------
Corporate | Y2K
Corporate | Annual Audit
Finance | Y2K
(3 rows)
Of course, this has little meaning without an aggregate. All you're
doing is leveraging GROUP BY's sort. You might as well use DISTINCT.
More useful would be:

[test@lexus] select dept, count(project) from projects group by dept;
dept | count
-----------+-------
Finance | 1
Corporate | 2
(2 rows)

or perhaps:

[test@lexus] select count(dept), project from projects group by project;
count | project
-------+--------------
2 | Y2K
1 | Annual Audit
This isn't my query, I'm translating a system prototyped in MSSQL to
Postgres. This query _does_ work in MSSQL. Does that constitute a
bug in MSSQL, or a shortcomming of Postgres, or just a difference of
interpretation?


If MSSQL picks an arbitrary value for the non-group by attribute, it
is violating spec.

Mike Mascari

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 22 '05 #4
Bill Moran said:
Like I said, the most important part (to me) is to understand why
Postgres refuses to run this. The fact that I don't know why points
to an obvious lack of understanding on my account, and I'd like to
remedy that :D
I have always assumed that you had to place all (non aggregated) columns
in your select in the "group by" clause as well. I suspect that the other
database isn't so picky (or is incorrect?).

Presumably changing the query to:
select id, name from gov_capital_pro ject group by id, name order by name;
works fine?
To that effect, if anyone can point me to a doc that will help me
gain a better understanding of why this error occurs, I'd be happy
to read it!


Have a look at:
http://www.postgresql.org/docs/7.4/s...ml#SQL-GROUPBY

[excerpted text]

GROUP BY Clause

The optional GROUP BY clause has the general form

GROUP BY expression [, ...]

GROUP BY will condense into a single row all selected rows that share the
same values for the grouped expressions. expression can be an input column
name, or the name or ordinal number of an output column (SELECT list
item), or an arbitrary expression formed from input-column values. In case
of ambiguity, a GROUP BY name will be interpreted as an input-column name
rather than an output column name.

Aggregate functions, if any are used, are computed across all rows making
up each group, producing a separate value for each group (whereas without
GROUP BY, an aggregate produces a single value computed across all the
selected rows). 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.

John Sidney-Woollett

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

Nov 22 '05 #5
On Fri, 27 Feb 2004, Bill Moran wrote:
Hey all.

I've hit an SQL problem that I'm a bit mystified by. I have two different
questions regarding this problem: why? and how do I work around it?

The following query:

SELECT GCP.id,
GCP.Name
FROM Gov_Capital_Pro ject GCP,
WHERE TLM.TLI_ID = $2
group by GCP.id
ORDER BY gcp.name;

Produces the following error:

ERROR: column "gcp.name" must appear in the GROUP BY clause or be used in an aggregate function


OK, let's look at a test table:

id | data
---------
0 | 'abc'
0 | 'def'
1 | 'ghi'

Now, let's use this query:

select id, data from test_table group by id;

what results should I get back?

I have two possible results for the data column, abc and def. But I only
get one row with a 0 in it, so which one of those do I pick?

If I use an aggregate I can be sure to get the first or last one:

select id, max(data) from test_table group by id;

Also, you may want to look at postgresql's extension, "distinct on":

http://www.postgresql.org/docs/7.4/s...ERIES-DISTINCT

It can give you the kind of results you want.

select distinct on (id) id, data from test_table;

But is know to be indeterminate, so you may get different results each
time.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 22 '05 #6
On Friday 27 February 2004 16:39, Bill Moran wrote:
John Sidney-Woollett wrote:
Bill Moran said:

SELECT GCP.id,
GCP.Name
FROM Gov_Capital_Pro ject GCP,
WHERE TLM.TLI_ID = $2
group by GCP.id
ORDER BY gcp.name; ERROR: column "gcp.name" must appear in the GROUP BY clause or be used
in an aggregate function
Like I said, the most important part (to me) is to understand why
Postgres refuses to run this. The fact that I don't know why points
to an obvious lack of understanding on my account, and I'd like to
remedy that :D


Like the error message says, if you're using GROUP BY everything in the SELECT
list must be an aggregate SUM(...) or used in the GROUP BY.

So, this is OK:
SELECT dept, week, SUM(amt_sold)
FROM weekly_sales
GROUP BY dept,week;
This isn't:
SELECT dept, week, SUM(amt_sold)
FROM weekly_sales
GROUP BY dept;

Ask yourself which "week" should be returned in the second case.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 22 '05 #7
On Fri, Feb 27, 2004 at 11:11:28AM -0500, Bill Moran wrote:
Hey all.

I've hit an SQL problem that I'm a bit mystified by. I have two different
questions regarding this problem: why? and how do I work around it?

The following query:

SELECT GCP.id,
GCP.Name
FROM Gov_Capital_Pro ject GCP,
WHERE TLM.TLI_ID = $2
group by GCP.id
ORDER BY gcp.name;

Produces the following error:

ERROR: column "gcp.name" must appear in the GROUP BY clause or be used in
an aggregate function

That field is a CHAR, so I'm not sure what kind of aggregate to use,
or (more important to my understanding) why one is necessary.

As I said, I'm not sure I understand why this occurs. I'm assuming that I
don't understand "group by" as well as I thought I did ;)

This isn't my query, I'm translating a system prototyped in MSSQL to
Postgres. This query _does_ work in MSSQL. Does that constitute a
bug in MSSQL, or a shortcomming of Postgres, or just a difference of
interpretation?


Well, if "non-standard" == "bug", then it's a bug in mssql. Your query
doesn't make any sense. What value for "Name" should be chosen if
there's more than one?

Michael
--
Michael Darrin Chaney
md******@michae lchaney.com
http://www.michaelchaney.com/

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 22 '05 #8
Mike Mascari <ma*****@mascar i.com> writes:
Bill Moran wrote:
SELECT GCP.id,
GCP.Name
FROM Gov_Capital_Pro ject GCP,
WHERE TLM.TLI_ID = $2
group by GCP.id
ORDER BY gcp.name;

ERROR: column "gcp.name" must appear in the GROUP BY clause or be used
in an aggregate function

This isn't my query, I'm translating a system prototyped in MSSQL to
Postgres. This query _does_ work in MSSQL. Does that constitute a
bug in MSSQL, or a shortcomming of Postgres, or just a difference of
interpretation?
If MSSQL picks an arbitrary value for the non-group by attribute, it
is violating spec.


They might be operating per spec. If "id" is a primary or unique key
for the table, then SQL99 (but not SQL92) says that it's sufficient to
group by the id column; the database is supposed to realize that the
other columns can't have more than one value per group, and allow direct
references to them. Or at least that's my interpretation of the pages
and pages in SQL99 about functional dependency. It seems like a pretty
useless frammish ... if you know that id is unique, why are you
bothering with GROUP BY at all?

Anyway, Postgres currently implements the SQL92 definition, which is
that you can't refer to an ungrouped column except within an aggregate
function call. So you need to call out all the columns to be referenced
in GROUP BY.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 22 '05 #9
Mike Mascari wrote:
Bill Moran wrote:
Hey all.

I've hit an SQL problem that I'm a bit mystified by. I have two
different
questions regarding this problem: why? and how do I work around it?

The following query:

SELECT GCP.id,
GCP.Name
FROM Gov_Capital_Pro ject GCP,
WHERE TLM.TLI_ID = $2
group by GCP.id
ORDER BY gcp.name;

Produces the following error:

ERROR: column "gcp.name" must appear in the GROUP BY clause or be
used in an aggregate function


The reason the grouping requires either an attribute to be aggregated or
apart of the group by list is that if it were not, an arbitrary value
would have to be selected:


Thanks to everyone who responded. All the replies have been very helpful.

Talking with the originator of the SQL statement, I came up with this:

select id, max(name) from gov_capital_pro ject group by id order by name;
ERROR: column "gov_capital_pr oject.name" must appear in the GROUP BY clause or be used in an aggregate function

I turned that over in my head a little and tried this:
select id, max(name) from gov_capital_pro ject group by id order by MAX(name);

Which finally works! As far as I understand it, that query will supply the
same results as they were getting from MSSQL on the previous query.

A little more playing around shows that this also works:
select id, max(name) as name from gov_capital_pro ject group by id order by name;

Which will probably be a little faster since MAX() is evaluated less.

Now I'm starting to see (maybe) why the query worked under MSSQL. the
MSSQL version had:

SELECT id as [ID], max(name) as [Name] from gov_capital_pro ject group by id order by name;

I'm guessing that MSSQL is fuzzy enought to figure that "group by name" actually
means "group by [Name]"?

--
Bill Moran
Potential Technologies
http://www.potentialtech.com
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

Nov 22 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
1477
by: Roumen Semov | last post by:
Hello, everyone! Does anyone know how I can pull additional field in a database when the max() of one field is pulled. For example: ===================================================== SELECT TOP 200 foreign_id, MAX(recordcreateddatetime) AS max_recordcreateddatetime FROM table1 GROUP BY foreign_id ===================================================== Here I am trying to pull the records that have the latest date for
3
2212
by: ChrisRath | last post by:
I have a table that I want to have a precalulcate length on a character field and group and sum up. Thought I could do this by creating a view with a group by clause that includes the sum function. Unfortunately, the compiler complains with: A clustered index cannot be created on the view 'MyView' because the index key includes columns which are not in the GROUP BY clause. Wish I could verbalize the problem a little better, but the...
0
2799
by: Pat S | last post by:
I have a report in which each record is about work booked to be done by my company for a client. Each day of work is represented by one record. The report is grouped by CLIENT -- so each page has a group header with the client's info, and under that a detail of work done or to be done within the date range specified when opening the report. The CLIENT Group footer has totals and other calulated fields...each page is that particular...
1
2856
by: A_PK | last post by:
I got one field, NOTE, its type is Ntext field. I have problem when query the statement using GROUP BY, could some pls kindly guide me. please found the sql query below... SELECT tblParent.indexno, tblParent.custno, tblParent.compname, tblParent.tt, tblParent.ordnotes FROM tblParent INNER JOIN tblChild ON tblParent.tid = tblChild.tid
4
1551
by: - | last post by:
I had added a new field to a form, and try insert the data to the new created table field. But when i try to assign the a control source for that form field, in the control source drop down list it doesn't show the new field from that table. Got any solution to solve this problem?
0
1363
by: willemp | last post by:
i have a report with a 3 groups in it A - project B - person each record in the report consists of a single realised workday on a single project by a single person. so i have records that contain projectname,personname,date,worked hours, and the hours that where planned for this person on this project
3
1498
by: veer | last post by:
Hi i run this query it works fine Select Yp1VOp,count(*) as instrec into instrec from " & txttablename & " where Yp1EOp=Yp1VOp Group By Yp1EOp,Yp1Vop Order By Yp1EOp,Yp1VOp but when i put a column name Yp1EOp = " " it produce the error the qeury like this Select Yp1VOp,count(*) as instrec into instrec from " & txttablename & " where Yp1EOp=Yp1VOp OR Yp1EOp = " " Group By Yp1EOp,Yp1Vop Order By Yp1EOp,Yp1VOp but i want Yp1EOp...
2
1598
by: AdamOnAccess | last post by:
Below is the SQL to a query that combines "Sum", "Group By" and "Count". In the case below, the field "ad group" is supposed to be counted and appear in a new field called "CountOfadGroupId". The value it returns 170 and that is incorrect. It should be 14. When I switch the "count" to "group by", the query returns a list of the 14 rows I expected. How come I can't get the "count" to work? Also, if you can offer any advice or links so I can...
3
3673
by: gershwyn | last post by:
I have a report that summarizes the costs for various projects, based off a query called ProjectCosts (outlined below.) The report is grouped first by companyNumber, then by branch. Branch can be null for companies that have only one location. The problem I am having is that I put a text box in the branch GroupHeader which displays a title for that section of the report. The controlSource for this control is: ="Current Projects for " & Nz(, )...
0
9895
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10666
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10724
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10351
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9503
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7071
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5929
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4546
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4140
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.