SQL Select mit SUM aus einer DB

[JonnyRico]

Hallo,

mal eine Frage. Vielleicht kann mir jemand einen Tip geben wie ich das am besten löse. Ich habe eine Tabelle in der für alle Kunden jeden Monat ein Datensatz mit Umsätzen steht. Nun will ich die Umsätze pro Jahr summieren und quasi als "Spaltenköpfe" bzw. Felder haben. Geht das in einem Statement? Wäre super wenn mir da jemand einen Tip geben könnten.

Gruß

Sascha

[B.Hauser]

Versuchs mal so:

Select Kunde, Sum(Case When Jahr = 2003 then Umsatz else 0 End),
              Sum(Case When Jahr = 2004 then Umsatz else 0 End)      
...
From MyTable
Where ...
Group By Kunde 

Birgitta

[Fuerchau]

Als kleine Ergänzung: man sollte NULL statt 0 nehmen, da dann vergleichbare Funktionen verwendet werden können:

Sum(Case When Year(Datum) = 2003 then Wert else NULL End) as Sum2003,
Avg(Case When Year(Datum) = 2003 then Wert else NULL End) as Avg2003 ...

Wird 0 verwendet, geht die Anzahl in den Count für AVG, bei NULL nicht.

[JonnyRico]

Hallo,

danke euch beiden. Klappt wunderbar. Ich habe ergänzend dazu noch mal eine Frage. Kann ich auch noch in dem selben Statement abfragen, das ich Zeilen in denen pro Kundennummer in den Ergebnisfeldern "SUM1", "SUM2",.... NULL steht, raus haben möchte.

Gruß

Sascha

[Fuerchau]

Hierfür dient dann die HAVING-Klausel.
Allerdings muss da der komplette Befehl wiederholt werden:

select ...
from
where
group
having
Sum(Case When Year(Datum) in (2003, 2004, ...) then Wert else 0 End) <> 0

Man beachte hierbei, dass du nicht NULL nehmen solltest, da du sonst 2 Abfragen benötigst:

having
Sum(Case When Year(Datum) in (2003, 2004, ...) then Wert else 0 End) <> 0 or
not Sum(Case When Year(Datum) in (2003, 2004, ...) then Wert else NULL End) is NULL

[B.Hauser]

Hierfür dient dann die HAVING-Klausel.
Allerdings muss da der komplette Befehl wiederholt werden:

select ...
from
where
group
having
Sum(Case When Year(Datum) in (2003, 2004, ...) then Wert else 0 End) <> 0

Man beachte hierbei, dass du nicht NULL nehmen solltest, da du sonst 2 Abfragen benötigst:

having
Sum(Case When Year(Datum) in (2003, 2004, ...) then Wert else 0 End) <> 0 or
not Sum(Case When Year(Datum) in (2003, 2004, ...) then Wert else NULL End) is NULL

Da würde ich denn doch lieber eine Common Table Expression verwenden:

With X as (Select Kunde, Sum(Case When Jahr = 2003 
                                  then Umsatz else 0 End) as Ums2003, 
                         Sum(Case When Jahr = 2004 
                                  then Umsatz else 0 End) as Ums2004,      
                         ... 
              From MyTable 
              Where ... 
              Group By Kunde)
Select * From X
   Where Ums2003 + Ums2004 + .... > 0 

Birgitta

[Fuerchau]

Das hängt jetzt stark vom Optimizer ab, welche Abfrage hier schneller ist.

Unabhängig davon, glaube ich, dass die X-Tabelle erst komplett aufgebaut wird, bevor die eigentliche Abfrage läuft.
Beim Having können die nicht relevanten Ergebnisse direkt verworfen werden.

Aber beim Optimizer waeiß man das ja nie. Bleibt einzig auszuprobieren.

[JonnyRico]

Wow. Mich erstaunen echt immer wieder die Möglichkeiten von SQL. Die Common Table Expression scheint ne tolle Sache zu sein. Ich weiß allerdings nicht ob es in meinem Fall funktioniert, da ich ein Insert mit Subselect macht. Da habe ich das mit der With-Anweisung nicht hin bekommen. Funktioniert es hier nicht und ich muss HAVING verwenden oder mache was falsch? Vielen Dank für eure Hilfe

Gruß

Sascha

[Fuerchau]

Beispiel eines Insert's mit "With" und glaube mir, der läuft seit V5R1:

-- Einfügen Ergebnis des nachfolgenden Select
-- ==========================================

insert into frct
(FCFIRM,
FCWKNR,
FCDATE,
FCSZNR,
FCLANR,
FCRSIA,
FCRSIB,
FCRSIC,
FCRSID,
FCRSEA,
FCRSEB,
FCRSEC,
FCRSED,
FCRSVA,
FCRSVB,
FCRSVC,
FCRSVD,
FCWCI0,
FCWCI1,
FCWCI2,
FCWCI3,
FCWCI4,
FCWCI5,
FCWCE0,
FCWCE1,
FCWCE2,
FCWCE3,
FCWCE4,
FCWCE5,
FCWCV0,
FCWCV1,
FCWCV2,
FCWCV3,
FCWCV4,
FCWCV5
)


-- Zwischenergebnisse, die im eigentlichen Select benötigt werden

with
-- Ermitteln Positionen Rückstand A Status 05 Inland
XPIA (FCRSIA) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01='05'and kdexkz='I' and p1lanr='D1'
and p1l4da <
year(current date) * 10000 +
month(current date) * 100 +
day(current date)
),

-- Ermitteln Positionen Rückstand A Status 05 Export
XPEA (FCRSEA) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01='05'and kdexkz='E' and p1lanr='D1'
and p1l4da <
year(current date) * 10000 +
month(current date) * 100 +
day(current date)
),

-- Ermitteln Positionen Rückstand A Status 05 VS
XPVA (FCRSVA) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01='05'and kdexkz='V' and p1lanr='D1'
and p1l4da <
year(current date) * 10000 +
month(current date) * 100 +
day(current date)
),

-- Ermitteln Positionen Rückstand B Status 12/15 Prio 50/51 Inland
XPIB (FCRSIB) as
(select count(*) from afp1, kdst, lgsl
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1firm=slfirm and p1wknr=slwknr
and p1afnr=slauft and p1afhp=slapos
and (p1st01='12' or P1ST01='15' ) and kdexkz='I' and p1lanr='D1'
and p1l4da <
year(current date) * 10000 +
month(current date) * 100 +
day(current date)
and (slprio=50 or slprio=51) and slstat < '80'
),

-- Ermitteln Positionen Rückstand B Status 12/15 Prio 50/51 Export
XPEB (FCRSEB) as
(select count(*) from afp1, kdst, lgsl
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1firm=slfirm and p1wknr=slwknr
and p1afnr=slauft and p1afhp=slapos
and (p1st01='12' or P1ST01='15' ) and kdexkz='E' and p1lanr='D1'
and p1l4da <
year(current date) * 10000 +
month(current date) * 100 +
day(current date)
and (slprio=50 or slprio=51) and slstat < '80'
),

-- Ermitteln Positionen Rückstand B Status 12/15 Prio 50/51 VS
XPVB (FCRSVB) as
(select count(*) from afp1, kdst, lgsl
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1firm=slfirm and p1wknr=slwknr
and p1afnr=slauft and p1afhp=slapos
and (p1st01='12' or P1ST01='15' ) and kdexkz='V' and p1lanr='D1'
and p1l4da <
year(current date) * 10000 +
month(current date) * 100 +
day(current date)
and (slprio=50 or slprio=51) and slstat < '80'
),

-- Ermitteln Positionen Rückstand C Status 12/15 Prio 40 Inland
XPIC (FCRSIC) as
(select count(*) from afp1, kdst, lgsl
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1firm=slfirm and p1wknr=slwknr
and p1afnr=slauft and p1afhp=slapos
and (p1st01='12' or P1ST01='15' ) and kdexkz='I' and p1lanr='D1'
and p1l4da <
year(current date) * 10000 +
month(current date) * 100 +
day(current date)
and slprio=40
),

-- Ermitteln Positionen Rückstand C Status 12/15 Prio 40 Export
XPEC (FCRSEC) as
(select count(*) from afp1, kdst, lgsl
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1firm=slfirm and p1wknr=slwknr
and p1afnr=slauft and p1afhp=slapos
and (p1st01='12' or P1ST01='15' ) and kdexkz='E' and p1lanr='D1'
and p1l4da <
year(current date) * 10000 +
month(current date) * 100 +
day(current date)
and slprio=40
),

-- Ermitteln Positionen Rückstand C Status 12/15 Prio 40 VS
XPVC (FCRSVC) as
(select count(*) from afp1, kdst, lgsl
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1firm=slfirm and p1wknr=slwknr
and p1afnr=slauft and p1afhp=slapos
and (p1st01='12' or P1ST01='15' ) and kdexkz='V' and p1lanr='D1'
and p1l4da <
year(current date) * 10000 +
month(current date) * 100 +
day(current date)
and slprio=40
),

-- Ermitteln Positionen Rückstand D Status >15 <=40 Inland
XPID (FCRSID) as
(select count(*) from afp1, kdst, lgsl
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1firm=slfirm and p1wknr=slwknr
and p1afnr=slauft and p1afhp=slapos
and p1st01>'15' and P1ST01<='40' and kdexkz='I' and p1lanr='D1'
and p1l4da <
year(current date) * 10000 +
month(current date) * 100 +
day(current date)
),

-- Ermitteln Positionen Rückstand D Status >15 <=40 Export
XPED (FCRSED) as
(select count(*) from afp1, kdst, lgsl
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1firm=slfirm and p1wknr=slwknr
and p1afnr=slauft and p1afhp=slapos
and p1st01>'15' and P1ST01<='40' and kdexkz='E' and p1lanr='D1'
and p1l4da <
year(current date) * 10000 +
month(current date) * 100 +
day(current date)
),

-- Ermitteln Positionen Rückstand D Status >12 <=40 VS
XPVD (FCRSVD) as
(select count(*) from afp1, kdst, lgsl
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1firm=slfirm and p1wknr=slwknr
and p1afnr=slauft and p1afhp=slapos
and p1st01>'15' and P1ST01<='40' and kdexkz='V' and p1lanr='D1'
and p1l4da <
year(current date) * 10000 +
month(current date) * 100 +
day(current date)
),

-- Ermitteln Positionen Aktuelle Woche Status 02/04/05/12 Inland
XPWI0 (FCWCI0) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01>='02' and P1ST01<='15'
and p1st01<>'03' and kdexkz='I' and p1lanr='D1'
and p1l4da >=year(current date) * 10000 +
month(current date) * 100 +
day(current date)
and p1l4da < year(date(days(current date)+7)) * 10000 +
month(date(days(current date)+7)) * 100 +
day(date(days(current date)+7))
),

-- Ermitteln Positionen +1 Woche Status 02/04/05/12 Inland
XPWI1 (FCWCI1) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01>='02' and P1ST01<='15'
and p1st01<>'03' and kdexkz='I' and p1lanr='D1'
and p1l4da >=year(date(days(current date)+7)) * 10000 +
month(date(days(current date)+7)) * 100 +
day(date(days(current date)+7))
and p1l4da < year(date(days(current date)+14)) * 10000 +
month(date(days(current date)+14)) * 100 +
day(date(days(current date)+14))
),

-- Ermitteln Positionen +2 Woche Status 02/04/05/12 Inland
XPWI2 (FCWCI2) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01>='02' and P1ST01<='15'
and p1st01<>'03' and kdexkz='I' and p1lanr='D1'
and p1l4da >=year(date(days(current date)+14)) * 10000 +
month(date(days(current date)+14)) * 100 +
day(date(days(current date)+14))
and p1l4da < year(date(days(current date)+21)) * 10000 +
month(date(days(current date)+21)) * 100 +
day(date(days(current date)+21))
),

-- Ermitteln Positionen +3 Woche Status 02/04/05/12 Inland
XPWI3 (FCWCI3) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01>='02' and P1ST01<='15'
and p1st01<>'03' and kdexkz='I' and p1lanr='D1'
and p1l4da >=year(date(days(current date)+21)) * 10000 +
month(date(days(current date)+21)) * 100 +
day(date(days(current date)+21))
and p1l4da < year(date(days(current date)+28)) * 10000 +
month(date(days(current date)+28)) * 100 +
day(date(days(current date)+28))
),

-- Ermitteln Positionen +4 Woche Status 02/04/05/12 Inland
XPWI4 (FCWCI4) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01>='02' and P1ST01<='15'
and p1st01<>'03' and kdexkz='I' and p1lanr='D1'
and p1l4da >=year(date(days(current date)+28)) * 10000 +
month(date(days(current date)+28)) * 100 +
day(date(days(current date)+28))
and p1l4da < year(date(days(current date)+35)) * 10000 +
month(date(days(current date)+35)) * 100 +
day(date(days(current date)+35))
),

-- Ermitteln Positionen >4 Woche Status 02/04/05/12 Inland
XPWI5 (FCWCI5) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01>='02' and P1ST01<='15'
and p1st01<>'03' and kdexkz='I' and p1lanr='D1'
and p1l4da >=year(date(days(current date)+35)) * 10000 +
month(date(days(current date)+35)) * 100 +
day(date(days(current date)+35))
),

-- Ermitteln Positionen Aktuelle Woche Status 02/04/05/12 Export
XPWE0 (FCWCE0) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01>='02' and P1ST01<='15'
and p1st01<>'03' and kdexkz='E' and p1lanr='D1'
and p1l4da >=year(current date) * 10000 +
month(current date) * 100 +
day(current date)
and p1l4da < year(date(days(current date)+7)) * 10000 +
month(date(days(current date)+7)) * 100 +
day(date(days(current date)+7))
),

-- Ermitteln Positionen +1 Woche Status 02/04/05/12 Export
XPWE1 (FCWCE1) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01>='02' and P1ST01<='15'
and p1st01<>'03' and kdexkz='E' and p1lanr='D1'
and p1l4da >=year(date(days(current date)+7)) * 10000 +
month(date(days(current date)+7)) * 100 +
day(date(days(current date)+7))
and p1l4da < year(date(days(current date)+14)) * 10000 +
month(date(days(current date)+14)) * 100 +
day(date(days(current date)+14))
),

-- Ermitteln Positionen +2 Woche Status 02/04/05/12 Export
XPWE2 (FCWCE2) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01>='02' and P1ST01<='15'
and p1st01<>'03' and kdexkz='E' and p1lanr='D1'
and p1l4da >=year(date(days(current date)+14)) * 10000 +
month(date(days(current date)+14)) * 100 +
day(date(days(current date)+14))
and p1l4da < year(date(days(current date)+21)) * 10000 +
month(date(days(current date)+21)) * 100 +
day(date(days(current date)+21))
),

-- Ermitteln Positionen +3 Woche Status 02/04/05/12 Export
XPWE3 (FCWCE3) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01>='02' and P1ST01<='15'
and p1st01<>'03' and kdexkz='E' and p1lanr='D1'
and p1l4da >=year(date(days(current date)+21)) * 10000 +
month(date(days(current date)+21)) * 100 +
day(date(days(current date)+21))
and p1l4da < year(date(days(current date)+28)) * 10000 +
month(date(days(current date)+28)) * 100 +
day(date(days(current date)+28))
),

-- Ermitteln Positionen +4 Woche Status 02/04/05/12 Export
XPWE4 (FCWCE4) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01>='02' and P1ST01<='15'
and p1st01<>'03' and kdexkz='E' and p1lanr='D1'
and p1l4da >=year(date(days(current date)+28)) * 10000 +
month(date(days(current date)+28)) * 100 +
day(date(days(current date)+28))
and p1l4da < year(date(days(current date)+35)) * 10000 +
month(date(days(current date)+35)) * 100 +
day(date(days(current date)+35))
),

-- Ermitteln Positionen >4 Woche Status 02/04/05/12 Export
XPWE5 (FCWCE5) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01>='02' and P1ST01<='15'
and p1st01<>'03' and kdexkz='E' and p1lanr='D1'
and p1l4da >=year(date(days(current date)+35)) * 10000 +
month(date(days(current date)+35)) * 100 +
day(date(days(current date)+35))
),

-- Ermitteln Positionen Aktuelle Woche Status 02/04/05/12 VS
XPWV0 (FCWCV0) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01>='02' and P1ST01<='15'
and p1st01<>'03' and kdexkz='V' and p1lanr='D1'
and p1l4da >=year(current date) * 10000 +
month(current date) * 100 +
day(current date)
and p1l4da < year(date(days(current date)+7)) * 10000 +
month(date(days(current date)+7)) * 100 +
day(date(days(current date)+7))
),

-- Ermitteln Positionen +1 Woche Status 02/04/05/12 VS
XPWV1 (FCWCV1) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01>='02' and P1ST01<='15'
and p1st01<>'03' and kdexkz='V' and p1lanr='D1'
and p1l4da >=year(date(days(current date)+7)) * 10000 +
month(date(days(current date)+7)) * 100 +
day(date(days(current date)+7))
and p1l4da < year(date(days(current date)+14)) * 10000 +
month(date(days(current date)+14)) * 100 +
day(date(days(current date)+14))
),

-- Ermitteln Positionen +2 Woche Status 02/04/05/12 VS
XPWV2 (FCWCV2) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01>='02' and P1ST01<='15'
and p1st01<>'03' and kdexkz='V' and p1lanr='D1'
and p1l4da >=year(date(days(current date)+14)) * 10000 +
month(date(days(current date)+14)) * 100 +
day(date(days(current date)+14))
and p1l4da < year(date(days(current date)+21)) * 10000 +
month(date(days(current date)+21)) * 100 +
day(date(days(current date)+21))
),

-- Ermitteln Positionen +3 Woche Status 02/04/05/12 VS
XPWV3 (FCWCV3) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01>='02' and P1ST01<='15'
and p1st01<>'03' and kdexkz='V' and p1lanr='D1'
and p1l4da >=year(date(days(current date)+21)) * 10000 +
month(date(days(current date)+21)) * 100 +
day(date(days(current date)+21))
and p1l4da < year(date(days(current date)+28)) * 10000 +
month(date(days(current date)+28)) * 100 +
day(date(days(current date)+28))
),

-- Ermitteln Positionen +4 Woche Status 02/04/05/12 VS
XPWV4 (FCWCV4) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01>='02' and P1ST01<='15'
and p1st01<>'03' and kdexkz='V' and p1lanr='D1'
and p1l4da >=year(date(days(current date)+28)) * 10000 +
month(date(days(current date)+28)) * 100 +
day(date(days(current date)+28))
and p1l4da < year(date(days(current date)+35)) * 10000 +
month(date(days(current date)+35)) * 100 +
day(date(days(current date)+35))
),

-- Ermitteln Positionen >4 Woche Status 02/04/05/12 Export
XPWV5 (FCWCV5) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01>='02' and P1ST01<='15'
and p1st01<>'03' and kdexkz='V' and p1lanr='D1'
and p1l4da >=year(date(days(current date)+35)) * 10000 +
month(date(days(current date)+35)) * 100 +
day(date(days(current date)+35))
),


-- nächste Laufende Nummer
XLFNR (FCFIRM, FCWKNR, FCDATE, FCSZNR, FCLANR) as
(select '2', '001',
year(current date) * 10000 +
month(current date) * 100 +
day(current date), max(FCSZNR) + 1, 'D1'
from frct
where fcfirm='2' and fcwknr='001' and fclanr ='D1'
)
(select '2', '001',
year(current date) * 10000 +
month(current date) * 100 +
day(current date), max(FCSZNR) + 1, 'D1'
from frct
where fcfirm='2' and fcwknr='001' and fclanr ='D1'
)

-- Zusammenführen aller Daten in 1 Zeile
-- =====================================

select * from XLFNR, XPIA, XPIB, XPIC, XPID,
XPEA, XPEB, XPEC, XPED,
XPVA, XPVB, XPVC, XPVD,
XPWI0, XPWI1, XPWI2, XPWI3, XPWI4, XPWI5,
XPWE0, XPWE1, XPWE2, XPWE3, XPWE4, XPWE5,
XPWV0, XPWV1, XPWV2, XPWV3, XPWV4, XPWV5

[woki]

Hallo Forum,
dieses Thema hat mir schon weitergeholfen, ich habe aber noch folgendes Problem:

Mit diesem SQL-Statement bekomme ich die Anzahl der Rechnungen pro Kunde für das Jahr 2006

Select 'AR'  , KZ05RG, KZ06RG, PROGRG, 
       VKGBRG, KDA5RG,             
      (CASE WHEN KDI8RG <> 0 THEN KDI8RG ELSE KDA8RG END), 
       RGJJRG,
       Count(distinct(BENRRG))
From     INFRGP
Where    RGJJRG = 2006
Group by KZ05RG, KZ06RG, PROGRG, VKGBRG, 
         KDA5RG, KDI8RG, KDA8RG, RGJJRG 

Bei dem Versuch, die Anzahl der Rechnungen pro Monat (RGMMRG) herauszufiltern, scheiter ich.
Wie würde das aussehen?

[Fuerchau]

Select 'AR' , KZ05RG, KZ06RG, PROGRG,
VKGBRG, KDA5RG,
(CASE WHEN KDI8RG <> 0 THEN KDI8RG ELSE KDA8RG END),
RGJJRG, RGMMRG
Count(distinct(BENRRG))
From INFRGP
Where RGJJRG = 2006
Group by KZ05RG, KZ06RG, PROGRG, VKGBRG,
KDA5RG, KDI8RG, KDA8RG, RGJJRG, RGMMRG

[woki]

Ich möchte nicht für jeden Monat einen Datensatz, sondern die Anzahl der Rechnungen für jeden Monat in einem Datensatz.
Als Beispiel hier der die Summes des Warenwerts für jeden Monat und Gesamtsumme:

Select 'UI'  , KZ05RG, KZ06RG, PROGRG, 
       VKGBRG, KDA5RG,             
      (CASE WHEN KDI8RG <> 0 THEN KDI8RG ELSE KDA8RG END), 
       RGJJRG,
       Sum(Case When RGMMRG =  1 then BWESRG else 0 End),
       Sum(Case When RGMMRG =  2 then BWESRG else 0 End),
       Sum(Case When RGMMRG =  3 then BWESRG else 0 End),
       Sum(Case When RGMMRG =  4 then BWESRG else 0 End),
       Sum(Case When RGMMRG =  5 then BWESRG else 0 End),
       Sum(Case When RGMMRG =  6 then BWESRG else 0 End),
       Sum(Case When RGMMRG =  7 then BWESRG else 0 End),
       Sum(Case When RGMMRG =  8 then BWESRG else 0 End),
       Sum(Case When RGMMRG =  9 then BWESRG else 0 End),
       Sum(Case When RGMMRG = 10 then BWESRG else 0 End),
       Sum(Case When RGMMRG = 11 then BWESRG else 0 End),
       Sum(Case When RGMMRG = 12 then BWESRG else 0 End),
       Sum(BWESRG) as GesamtSumme
From     INFRGP
Where    RGJJRG = 2006
Group by KZ05RG, KZ06RG, PROGRG, VKGBRG, 
         KDA5RG, KDI8RG, KDA8RG, RGJJRG 

Hier sagt SQL/400 "Spalte RGMMRG oder Ausdruck in SELECT-Liste nicht gültig."

 Select 'AR'  , KZ05RG, KZ06RG, PROGRG,                              
       VKGBRG, KDA5RG,                                              
      (CASE WHEN KDI8RG <> 0 THEN KDI8RG ELSE KDA8RG END),          
       RGJJRG,                                                      
      (Case When RGMMRG =  1 then Count(distinct(BENRRG)) else 0 End),
       Count(distinct(BENRRG))                                      
From     INFRGP                                                     
Where    RGJJRG = 2006                                              
Group by KZ05RG, KZ06RG, PROGRG, VKGBRG,                            
         KDA5RG, KDI8RG, KDA8RG, RGJJRG 

Da muß es doch eine Lösung geben....