[Oracle] not a "Group By" expression, Problem mit inner SELECT

[Scratch]

Hmm ... ein SQL will nicht so recht:

SELECT 

TRIM(TO_CHAR(m1.mdate,'D')) AS mday

,SUM(m1.mood * m1.mcount) / SUM(m1.mcount) AS maverage

,SUM(m1.mcount) / (SELECT count(distinct m2.mdate) FROM u_moodsensor.mood m2 WHERE TO_CHAR(m2.mdate,'D')=TO_CHAR(m1.mdate,'D') GROUP BY TO_CHAR(m2.mdate,'D')) AS mcount

FROM u_moodsensor.mood m1 

GROUP BY TO_CHAR(m1.mdate,'D')

ORDER BY 1;

=> dieses geht nicht ... Aber dieses geht einwandfrei (TO_CHAR(m1.mdate,'D') mit '2' ersetzt)

SELECT 

TRIM(TO_CHAR(m1.mdate,'D')) AS mday

,SUM(m1.mood * m1.mcount) / SUM(m1.mcount) AS maverage

,SUM(m1.mcount) / (SELECT count(distinct m2.mdate) FROM u_moodsensor.mood m2 WHERE TO_CHAR(m2.mdate,'D')='2' GROUP BY TO_CHAR(m2.mdate,'D')) AS mcount

FROM u_moodsensor.mood m1 

GROUP BY TO_CHAR(m1.mdate,'D')

ORDER BY 1;

Weiss jemand wieso?

Grüße

scratch

[Scratch]

Hab die Lösung ... ich habs mir komplizierter gemacht als ich musste

So gehts (ohne Subquery):

SELECT 

TRIM(TO_CHAR(mdate,'D')) AS mday

,SUM(mood * mcount) / SUM(mcount) AS maverage

,SUM(mcount) / COUNT(DISTINCT mdate) AS mcount 

FROM u_moodsensor.mood 

GROUP BY TRIM(TO_CHAR(mdate,'D')) 

ORDER BY 1