Summen aus definierten Gruppen bestimmen?

[Martin R.]

Hallo Zusammen,

stehe gerade etwas auf dem Schlauch, evtl 1 Tag nach dem Urlaub

Ich habe folgendes Problem.

Ich benötige das avg und/oder die Summe gruppiert nach bestimmten Altersgruppen.

Die Anzahl habe ich folgender Massen ermittelt.
[highlight=sql]
WITH Tabelle1 AS
(
SELECT 'Hans'Name,'1' bpnr, '12.12.2011' Datum FROM dual UNION ALL
SELECT 'Hans', '2', '13.12.2007' FROM dual UNION ALL
SELECT 'Hans', '3', '14.12.2001' FROM dual UNION ALL
SELECT 'Hans', '4', '15.12.1963' FROM dual UNION ALL
SELECT 'Hans', '5', '16.12.1977' FROM dual UNION ALL
SELECT 'Hans', '6', '17.12.2010' FROM dual UNION ALL
SELECT 'klaus', '7', '18.12.2000' FROM dual UNION ALL
SELECT 'jochen','8', '12.12.1911' FROM dual UNION ALL
SELECT 'Franz', '9', '13.12.1958' FROM dual UNION ALL
SELECT 'Franz', '10', '14.12.1943' FROM dual UNION ALL
SELECT 'Franz', '11', '15.12.1968' FROM dual UNION ALL
SELECT 'Franz', '12', '16.12.1977' FROM dual UNION ALL
SELECT 'Franz', '13', '17.12.1988' FROM dual UNION ALL
SELECT 'Franz', '14', '18.12.203' FROM dual
where 1=1),
Tabelle2 AS
(
SELECT '12' betrag ,'11' bpnr, '17' betrag2 FROM dual UNION ALL
SELECT '22' betrag ,'2', '22' FROM dual UNION ALL
SELECT '22' betrag ,'3', '2011' FROM dual UNION ALL
SELECT '11' betrag ,'4', '17' FROM dual UNION ALL
SELECT '22' betrag ,'5', '289' FROM dual UNION ALL
SELECT '44' betrag ,'6', '33' FROM dual UNION ALL
SELECT '55' betrag ,'7', '44' FROM dual UNION ALL
SELECT '77' betrag ,'8', '22' FROM dual UNION ALL
SELECT '12' betrag ,'9', '11' FROM dual UNION ALL
SELECT '14' betrag ,'10', '14' FROM dual UNION ALL
SELECT '14' betrag ,'11', '15' FROM dual UNION ALL
SELECT '25' betrag ,'12', '16' FROM dual UNION ALL
SELECT '23' betrag ,'13', '77' FROM dual UNION ALL
SELECT '14' betrag ,'14', '2011' FROM dual
where 1=1)
SELECT
SUM ( CASE WHEN trunc(months_between('01.04.2011',Datum)/12) BETWEEN 0 AND 9 THEN 1 ELSE 0 END ) AS Gruppe_1,
SUM ( CASE WHEN trunc(months_between('01.04.2011',Datum)/12) BETWEEN 10 AND 19 THEN 1 ELSE 0 END ) AS Gruppe_2,
SUM ( CASE WHEN trunc(months_between('01.04.2011',Datum)/12) BETWEEN 20 AND 29 THEN 1 ELSE 0 END ) AS Gruppe_3,
SUM ( CASE WHEN trunc(months_between('01.04.2011',Datum)/12) BETWEEN 30 AND 39 THEN 1 ELSE 0 END ) AS Gruppe_4,
SUM ( CASE WHEN trunc(months_between('01.04.2011',Datum)/12) BETWEEN 40 AND 49 THEN 1 ELSE 0 END ) AS Gruppe_5,
SUM ( CASE WHEN trunc(months_between('01.04.2011',Datum)/12) BETWEEN 50 AND 59 THEN 1 ELSE 0 END ) AS Gruppe_6,
SUM ( CASE WHEN trunc(months_between('01.04.2011',Datum)/12) BETWEEN 60 AND 69 THEN 1 ELSE 0 END ) AS Gruppe_7,
SUM ( CASE WHEN trunc(months_between('01.04.2011',Datum)/12) BETWEEN 70 AND 79 THEN 1 ELSE 0 END ) AS Gruppe_8,
SUM ( CASE WHEN trunc(months_between('01.04.2011',Datum)/12) BETWEEN 80 AND 89 THEN 1 ELSE 0 END ) AS Gruppe_9,
SUM ( CASE WHEN trunc(months_between('01.04.2011',Datum)/12) >90 THEN 1 ELSE 0 END ) AS Gruppe_10
from Tabelle1 a join Tabelle2 b
on b.bpnr=a.bpnr
;[/highlight]

Welches sql nutze ich um avg oder sum aus der Summe der beiden Betraege zu ermitteln.
In jeder Tabelle sind um die 1 Mio DS
Wir nutzen 11g

Vielen Dank im voraus

Viele Grüße

MArtin

[Wernfried]

Helfen dir die Analytic Functions weiter?

Sieht ungefähr so aus: [highlight=sql]SUM(betrag) OVER (PARTITION BY Altersgruppe(Datum)),
AVG(betrag) OVER (PARTITION BY Altersgruppe(Datum)) [/highlight]

Unter Umständen musst du die Funktion für die Altergruppe erst noch definieren falls es nicht mit TRUNC(date, 'fmt') geht.

Gruss

[ralfb]

Hallo, Martin,

so etwa?

[highlight=sql]
...
SELECT '23' betrag ,'13', '77' FROM dual UNION ALL
SELECT '14' betrag ,'14', '2011' FROM dual
WHERE 1=1),
Tabelle3 as
(SELECT bpnr
, CASE WHEN trunc(months_between('01.04.2011',Datum)/12) BETWEEN 0 AND 9 THEN 1
WHEN trunc(months_between('01.04.2011',Datum)/12) BETWEEN 10 AND 19 THEN 2
WHEN trunc(months_between('01.04.2011',Datum)/12) BETWEEN 20 AND 29 THEN 3
WHEN trunc(months_between('01.04.2011',Datum)/12) BETWEEN 30 AND 39 THEN 4
WHEN trunc(months_between('01.04.2011',Datum)/12) BETWEEN 40 AND 49 THEN 5
WHEN trunc(months_between('01.04.2011',Datum)/12) BETWEEN 50 AND 59 THEN 6
WHEN trunc(months_between('01.04.2011',Datum)/12) BETWEEN 60 AND 69 THEN 7
WHEN trunc(months_between('01.04.2011',Datum)/12) BETWEEN 70 AND 79 THEN 8
WHEN trunc(months_between('01.04.2011',Datum)/12) BETWEEN 80 AND 89 THEN 9
ELSE 9 END AS Gruppe
FROM Tabelle1)
SELECT c.gruppe
,SUM(b.betrag) summe
,AVG(b.betrag) durch
FROM tabelle2 b,tabelle3 c
WHERE b.bpnr=c.bpnr
GROUP BY c.gruppe
ORDER BY c.gruppe
;
[/highlight]

    GRUPPE      SUMME      DURCH
         1         88 29,3333333
         2         55         55
         3         23         23
         4         47       23,5
         5         37 12,3333333
         6         12         12
         7         14         14
         9         91       45,5

Gruß

Ralf

[Martin R.]

Hallo Wernfried, hallo Ralf,

vielen Dank!

Funktioniert beides!

Ähnlich perfomant und performanter als ich dachte.
Für die es interesiert
Wenfried/Ralf=30800/29800.

Wobeich vermutet habe, dass das analyt. SQL performanter ist.

Vielen Dank nochmal Euch Beiden!

Viele Grüße

Martin