CREATE OR ALTER VIEW UMSATZPROMONAT(
ARTIKEL,
JAHR,
JANUAR,
FEBRUAR,
MAERZ,
APRIL,
MAI,
JUNI,
JULI,
AUGUST,
SEPTEMBER,
OKTOBER,
NOVEMBER,
DEZEMBER)
AS
select
Artikel as Artikel,
Jahr as jahr,
sum( Januar) as Januar,
sum( Februar) as Februar,
sum( Maerz) as Maerz,
sum( April) as April,
sum( Mai) as mai,
sum( Juni) as juni,
sum( Juli) as juli,
sum( August) as august,
sum( September) as september,
sum( Oktober) as oktober,
sum( November) as november,
sum( Dezember) as dezember
from
(
select
Artikel as Artikel,
Jahr as Jahr,
iif( monat = 1, umsatz, 0) as Januar,
iif( monat = 2, umsatz, 0) as Februar,
iif( monat = 3, umsatz, 0) as Maerz,
iif( monat = 4, umsatz, 0) as April,
iif( monat = 5, umsatz, 0) as Mai,
iif( monat = 6, umsatz, 0) as Juni,
iif( monat = 7, umsatz, 0) as Juli,
iif( monat = 8, umsatz, 0) as August,
iif( monat = 9, umsatz, 0) as September,
iif( monat = 10, umsatz, 0) as Oktober,
iif( monat = 11, umsatz, 0) as November,
iif( monat = 12, umsatz, 0) as Dezember
from
(
select
Artikel as Artikel,
extract( year from datum) as jahr,
extract( month from datum) as monat,
sum( umsatz) as umsatz
from
umsatz
group by
1,2,3
)
)
group by Artikel, Jahr
;