Received: from mail-spool3.eskom.co.za (mail-spool3.eskom.co.za [147.110.49.17]) by postgresql.org (Postfix) with ESMTP id 679C5475A01 for ; Thu, 11 Apr 2002 05:49:15 -0400 (EDT) Received: from eskom.co.za (gw.eskom.co.za [147.110.52.108]) by mail-spool3.eskom.co.za (8.11.5/8.11.5) with ESMTP id g3BBnF822088 for ; Thu, 11 Apr 2002 11:49:15 GMT Received: from ROUTING-MTA by eskom.co.za with Novell_GroupWise; Thu, 11 Apr 2002 11:49:19 +0200 Message-Id: X-Mailer: Novell GroupWise Internet Agent 6.0.1 Date: Thu, 11 Apr 2002 11:49:15 +0200 From: "Richard Ellerbrock" To: Subject: REPOST: Trouble with SQL conversion Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Archive-Number: 200204/130 X-Sequence-Number: 7395 I am trying to write portable sql which will work on multiple databases, but am having trouble converting this query to postgres 7.1.3: SELECT base.baseindex, base.subnetsize, base.descrip, base.baseaddr, base.admingrp, count(ipaddr.baseindex) AS cnt FROM base LEFT JOIN ipaddr ON base.baseindex=ipaddr.baseindex WHERE base.baseaddr BETWEEN 2473473024 AND 2473473151 AND base.customer=1 GROUP BY base.baseindex ORDER BY base.baseaddr; I get this error: 0: ERROR: Attribute base.subnetsize must be GROUPed or used in an aggregate function The table structures are: CREATE TABLE base ( baseaddr bigint NOT NULL default '0', subnetsize int NOT NULL default '0', descrip varchar(80) NOT NULL default '', baseindex serial, admingrp varchar(40) NOT NULL default '', customer smallint NOT NULL default '0', lastmod timestamp, userid varchar(40) NOT NULL default '', swipmod timestamp, PRIMARY KEY (baseindex), UNIQUE (baseaddr,customer) ); CREATE INDEX base_customer ON base (customer); CREATE INDEX base_admingrp ON base (admingrp); CREATE TABLE ipaddr ( ipaddr bigint NOT NULL default '0', userinf varchar(80) NOT NULL default '', location varchar(80) NOT NULL default '', telno varchar(15) NOT NULL default '', descrip varchar(80) NOT NULL default '', baseindex int NOT NULL default '0', lastmod timestamp NOT NULL, userid varchar(40) NOT NULL default '', PRIMARY KEY (ipaddr,baseindex) ); CREATE INDEX ipaddr_baseindex ON ipaddr (baseindex);