postgres - zapytanie
Jest takie zapytanie, które to działa w mysql a w postgresie nie chce
#v- SELECT id, lower(name) as UserName , 'Cleartext-Password' as Attribute , passwd as Value, ':=' as op FROM nodes WHERE name = 'serwis' UNION SELECT id, lower(name) as UserName , 'Simultaneous-Use' as Attribute, '1' as Value, ':=' as op FROM nodes WHERE name = 'serwis' UNION SELECT id AS id, lower(name) AS UserName, 'Calling-Station-Id' AS Attribute, mac as Value, '==' as op FROM vmacs m WHERE name ='serwis' AND EXISTS (SELECT 1 FROM nodes WHERE name= 'serwis' AND chkmac = 1) UNION SELECT 0 AS id, 'serwis' AS UserName, 'Max-Octets' AS Attribute, CONCAT(ROUND(COALESCE(x.dlimit, y.dlimit))) AS Value, ':=' AS op FROM ( SELECT n.id, MIN(n.name) AS name, SUM(t.dlimit) AS dlimit FROM nodeassignments na JOIN assignments a ON (na.assignmentid = a.id) JOIN tariffs t ON (a.tariffid = t.id) JOIN nodes n ON (na.nodeid = n.id) JOIN ( SELECT assignmentid, COUNT(*) AS cnt FROM nodeassignments GROUP BY assignmentid ) o ON (o.assignmentid = na.assignmentid) WHERE (a.datefrom <= EXTRACT(EPOCH FROM now()) OR a.datefrom = 0) AND (a.dateto > EXTRACT(EPOCH FROM now()) OR a.dateto = 0) AND a.suspended = 0 AND n.name = 'serwis' GROUP BY n.id ) x RIGHT JOIN ( SELECT SUM(t.dlimit)/o.cnt AS dlimit FROM assignments a JOIN tariffs t ON (a.tariffid = t.id) JOIN nodes n ON (a.customerid = n.ownerid) JOIN ( SELECT COUNT(*) AS cnt, ownerid FROM nodes WHERE NOT EXISTS ( SELECT 1 FROM nodeassignments, assignments a WHERE assignmentid = a.id AND nodeid = nodes.id AND a.suspended = 0 AND (a.dateto > EXTRACT(EPOCH FROM now()) OR a.dateto = 0)) GROUP BY ownerid ) o ON (o.ownerid = n.ownerid) WHERE (a.datefrom <= EXTRACT(EPOCH FROM now()) OR a.datefrom = 0) AND (a.dateto > EXTRACT(EPOCH FROM now()) OR a.dateto = 0) AND a.suspended = 0 AND t.dlimit != '0' AND NOT EXISTS ( SELECT 1 FROM nodeassignments WHERE assignmentid = a.id) AND n.name = 'serwis' GROUP BY n.id ) y ON (1=1) #v-
a postgres mówi: #v- BŁĄD: kolumna "o.cnt" musi występować w klauzuli GROUP BY lub być użyta w funkcji agregującej LINE 33: SELECT SUM(t.dlimit)/o.cnt AS dlimit ^
********** Error **********
BŁĄD: kolumna "o.cnt" musi występować w klauzuli GROUP BY lub być użyta w funkcji agregującej SQL state: 42803 Character: 1538 #v-
jak to poprawić?
z gory dzięki
Dnia 9 kwi 2015 o godz. 14:52 Marcin marcin@nicram.net napisał(a):
najprosciej tak jak sygeruje podpowiedz w postgres. Dodac do GROUP BY wszystko co w SELECT
Przynajmniej ja - nie programista - tak sobie radze z przerabianiem zapytan na posgres:p Czy dobrze czy nie - nie wiem, ale dziala :p
uczestnicy (3)
-
Andrzej Banach
-
Marcin
-
Tomasz Chiliński