473,889 Members | 1,862 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
12 25321
Tom Lane wrote:
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
interpretati on?
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?


It's possible that you're right about MSSQL, the column in question _is_
unique. I also had another theory (see other post).

As for why I'm using a GROUP BY: it's not my decision, I'm converting
SQL that someone else wrote, and (honestly) I don't understand the
data well enough to say whether it's required in this query or not.

Also, the _actual_ query that I'm converting here is more complex than
this (it's a join of 5 tables) but in my experimenting/testing, I found
that the query that I had minimized down to had the exact same behaviour.
So I posted the simplified query instead of the actual query, to make it
easier on those who would reply.

If you think it would help with Postgres' development, I'll give you
access to my development machine and the actual query involved. I'm
sure the client won't mind, since their banking their future on the
reliability of Postgres anyway ;)
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.


To me, that seems the most likely explanation (i.e. id is a primary key,
and MSSQL is SQL99 compliant)

--
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 #11
Hey guys

I have dealt with this before.

And there is a simple solution: If the value really is unique, just wrap it
in a max(). Since it's unique, it has *ZERO* effect on your output, but it
then complies to PostgreSQL's GROUP BY implementation, and hence will run...

Terry Fielder
Manager Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
te***@greatgulf homes.com
Fax: (416) 441-9085

-----Original Message-----
From: pg************* ****@postgresql .org
[mailto:pg****** ***********@pos tgresql.org]On Behalf Of Tom Lane
Sent: Friday, February 27, 2004 1:09 PM
To: Mike Mascari
Cc: Bill Moran; pg***********@p ostgresql.org
Subject: Re: [GENERAL] field must appear in the GROUP BY clause or be
used
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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 22 '05 #12
[ drifting a bit off the thread topic, but just for completeness... ]

Bill Moran <wm****@potenti altech.com> writes:
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);
...
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.


Actually I believe you'll get the exact same plan either way. GROUP and
ORDER BY expressions are merged with any matching SELECT-list entries
during parsing.

In fact, as of (I think) 7.4, the executor detects and eliminates
duplicate aggregate-function calls even when the parser didn't.
So for instance this:
SELECT max(x), max(x) + 1 FROM ...
will only run the MAX() aggregate once.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

Nov 23 '05 #13

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

Similar topics

3
1478
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
2213
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
2859
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
1364
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
1500
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
9810
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11203
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10794
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
7999
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7151
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
5830
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6029
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4650
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
3
3257
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.