hi der sagt mir hier bei meiner sql anweisung :
********** Fehler **********
ERROR: aggregates not allowed in GROUP BY clause
SQL Status:42803
Kontext:SQL statement " SELECT CASE WHEN avg(rv.value) IS NULL THEN 1 ELSE 0 END AS sorter, 0 AS isnormvalue, bases.name,'0' AS firstname, avg(rv.value) AS value, rv.testid, rv.testperiodid, ta.gender, tate.agegroupid FROM testperiods_bases bate, talents_testperiods tate, talents ta, resultsview rv, bases WHERE bate.testperiodid = $1 AND rv.testperiodid = $1 AND tate.testperiodid = $1 AND ta.id = tate.talentid AND tate.talentid = rv.talentid AND bate.baseid = bases.id AND bases.basegroupid = $2 AND tate.baseid = bate.baseid GROUP BY sorter, isnormvalue, bases.name, firstname, avg(rv.value), rv.testid, rv.testperiodid, ta.gender, tate.agegroupid ORDER BY testid, agegroupid, gender, value"
PL/pgSQL function "calculatebasegroupranking" line 5 at FOR over SELECT rows
Aufruf:
select * from calculatebasegroupranking(20041,3) as (sorter integer, isnormalue integer ,lastname text, firstname text, value numeric, testid integer, testperiodid integer, gender integer, agegroupid integer) order by testid,agegroupid ASC
Versteh den fehler nicht. kann mir jemand weiterhelfen?
********** Fehler **********
ERROR: aggregates not allowed in GROUP BY clause
SQL Status:42803
Kontext:SQL statement " SELECT CASE WHEN avg(rv.value) IS NULL THEN 1 ELSE 0 END AS sorter, 0 AS isnormvalue, bases.name,'0' AS firstname, avg(rv.value) AS value, rv.testid, rv.testperiodid, ta.gender, tate.agegroupid FROM testperiods_bases bate, talents_testperiods tate, talents ta, resultsview rv, bases WHERE bate.testperiodid = $1 AND rv.testperiodid = $1 AND tate.testperiodid = $1 AND ta.id = tate.talentid AND tate.talentid = rv.talentid AND bate.baseid = bases.id AND bases.basegroupid = $2 AND tate.baseid = bate.baseid GROUP BY sorter, isnormvalue, bases.name, firstname, avg(rv.value), rv.testid, rv.testperiodid, ta.gender, tate.agegroupid ORDER BY testid, agegroupid, gender, value"
PL/pgSQL function "calculatebasegroupranking" line 5 at FOR over SELECT rows
Code:
DECLARE
currentrow record;
BEGIN
FOR currentrow IN SELECT
CASE WHEN avg(rv.value) IS NULL THEN 1 ELSE 0 END AS sorter,
0 AS isnormvalue, bases.name,'0' AS firstname, avg(rv.value) AS value, rv.testid, rv.testperiodid, ta.gender, tate.agegroupid
FROM testperiods_bases bate, talents_testperiods tate, talents ta, resultsview rv, bases
WHERE bate.testperiodid = var_testperiodid AND rv.testperiodid = var_testperiodid AND tate.testperiodid = var_testperiodid
AND ta.id = tate.talentid AND tate.talentid = rv.talentid AND bate.baseid = bases.id AND bases.basegroupid = var_basegroupid AND tate.baseid = bate.baseid
GROUP BY sorter, isnormvalue, bases.name, firstname, value, rv.testid, rv.testperiodid, ta.gender, tate.agegroupid ORDER BY testid, agegroupid, gender, value LOOP
return next currentrow;
END LOOP;
return;
END;
Aufruf:
select * from calculatebasegroupranking(20041,3) as (sorter integer, isnormalue integer ,lastname text, firstname text, value numeric, testid integer, testperiodid integer, gender integer, agegroupid integer) order by testid,agegroupid ASC
Versteh den fehler nicht. kann mir jemand weiterhelfen?