I have 2 (8.4.11) servers that I am testing the following query:
SELECT count(*),
maptrunc(cpeloc.lat, 4.5)::text || maptrunc(cpeloc.long, 4.5)::text AS name,
AVG(cpeloc.lat) AS lt,
AVG(cpeloc.long) AS lng
FROM cable_billing JOIN cpeloc USING(mac) LEFT JOIN davic USING(mac)
WHERE (status = 0 OR status is null) AND (cpeloc.lat is not null AND cpeloc.lat !=0 AND cpeloc.long is not null AND cpeloc.long != 0)
GROUP BY name ORDER BY name;
On the first server the query works,
count | name | lt | lng
-------+-----------------+---------------------+----------------------
1 | 43.0425-94.2295 | 43.0429410000000000 | -94.2299740000000000
1 | 43.0525-94.260 | 43.0526200000000000 | -94.2603800000000000
1 | 43.054-94.224 | 43.0543150000000000 | -94.2244750000000000
(51 rows)
On the second server I get an error.
ERROR: column "cpeloc.lat" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select count(*), maptrunc(cpeloc.lat, 4.5)::text || maptrunc...
I was thinking the one server that works the maptrunc function was thought of as an agg function, but they are both defined the same
\df maptrunc
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+----------+------------------+---------------------+--------
public | maptrunc | numeric | numeric, numeric | normal
(1 row)
The only difference between the 2 servers are that the one that works returns 51 rows and the one that does not I would expect to return 12000 rows.
I can make the one that does not work functional by changing
maptrunc(cpeloc.lat)::text --> max(maptrunc(cpeloc.lat))::text
maptrunc(cpeloc.long)::text --> max(maptrunc(cpeloc.long))::text
however, that one breaks on the first server with the error
ERROR: aggregates not allowed in GROUP BY clause
LINE 1: select count(*), max(maptrunc(cpeloc.lat, 4.5))::text || max...
Any suggestions would be appreciated.
George Woodring
--
iGLASS Networks
www.iglass.net