Problem mit SQL-Query, GROUP BY

SideWinder

Ich habe eine 1:n-Relation Agent -> Policy (ein Agent ist mehreren Policies zugeordnet bzw. hat diese verkauft). Eine Policy hat ein created-Attribut (Verkaufsdatum).

Nun benötige ich folgendes Query:
Average count of policies agents sold per month

Ergebnis soll also circa so aussehen:

| 01/2010 | 3.4 |
| 02/2010 | 2.4 |
| 03/2010 | 3.6 |

Also im Januar 2010 hat im Durchschnitt jeder Agent 3,4 Policies verkauft.

Mein Test bis jetzt:

SELECT
concat(month(p.p_created), concat('/', year(p.p_created))) AS Month,
avg(count(p.p_id)) AS Cnt
FROM Policy p
INNER JOIN Agent a ON p.p_agent = a.a_id
GROUP BY a.a_id, Month

Funktioniert allerdings nicht, dass hier durch das zweifache gruppieren ein avg(count()) möglich wird. Er gruppiert ganz einfach nach beiden Attributen.

Wie funktioniert "sequentielles gruppieren"?

Edit: "DB" ist HSQLDB...

MfG SideWinder

SideWinder

SELECT Month, avg(Cnt) FROM
(
SELECT concat(month(p.p_created), concat('/', year(p.p_created))) AS Month, a.a_id, count(p.p_id) AS Cnt FROM Policy p JOIN Agent a ON p.p_agent = a.a_id GROUP BY a.a_id )
GROUP BY Month

Ist das wirklcih meine einzige Möglichkeit? Uiuiui, sieht gar nicht gut aus.

MfG SideWinder

hustbaer

mit MSSQL geht das so (abgesehen davon dass man eine kleinigkeit leicht anders schreiben muss):

SELECT 
concat(month(p.p_created), concat('/', year(p.p_created))) AS Month, 

  count(p.p_id)/count([i]DISTINCT[/i] p.p_agent) AS Cnt 

FROM Policy p 
INNER JOIN Agent a ON p.p_agent = a.a_id 
GROUP BY Month

keine ahnung ob COUNT(DISTINCT) mit HSQLDB auch geht...

wobei du natürlich agenten nicht mitzählst, wenn sie ein ganzes monat keine police verkauft haben. ich glaube nicht dass das im sinne des erfinders ist, aber deine originale abfrage würde das ja genauso machen

SideWinder

Stimmt. Was müsste ich da noch umstellen? Den INNER JOIN in einen RIGHT OUTER JOIN umändern?

Edit: Nein, das auch nicht. Wie kann ich dann überhaupt das Query so gestalten, dass immer alle Agents eingebunden werden?

MfG SideWinder

witte

dann löse das AVG doch auf:

SELECT Month,Amount/CAST((SELECT COUNT(*) FROM agent) AS numeric) AS Average
FROM (
SELECT DISTINCT to_char(p_created, 'MM/YYYY') AS Month, COUNT(*) AS Amount
FROM Policy
LEFT JOIN Agent ON p_agent = a_id
GROUP BY to_char(p_created, 'MM/YYYY')
)v

Syntax ist Postgresql, mußt du wieder umstellen.

/Edit: den Agenten brauchst du nicht joinen, wenn du danach nicht weiter fitlern willst...