Monatsliste mit left Join ?

[Conan]

Hallo Community,

nach einigem Kopfzerbrechen und erfolglosen Versuchen erbitte ich eure Hilfe:
In einer Access Datenbank habe ich eine Tabelle in der verschiedene Monate des Jahres, als Datumsfeld verschieden oft vorkommen. Jedoch kommt nicht jeder Monat in der Tabelle vor.
In einer Abfrage möchte nun aber eine Auflistung haben, in der in Spalte 1 alle Monate von Januar bis Dezember stehen und in Spalte 2 die Anzahl der Einträge für den jeweiligen Monat, wobei dann für den Monat, der in der Tabelle nicht auftaucht eine null erscheinen sollte.

Wenn ich die Abfrage einfach in der Form aufbaue... :
Select month(datum) as monat, count(month(datum)) as anzahl
From tabplanung
Group by month(datum)

... erhalte ich ja nur eine Liste für Monate in der Einträge stattgefunden haben.
Wenn ich nun aber immer alle Monate aufgelistet haben möchte, auch wenn in dem Monat kein Eintrag stattgefunden hat, muss ich dann ne neue Monatstabelle mit allen Monaten erstellen und leftjoinen ?
Wie baue ich die Abfrage auf ?
Das ganze sollte dann so aussehen mit 1 für Januar und 12 für Dezember
1 2
2 0
3 0
4 5
...
12 1

Wenn z.B. für Februar und März kein Datumseintrag vorhanden war.

 

[akretschmer]

In PostgreSQL könntest Du Deine Join-Table direkt on-the-fly erstellen, der Join ginge dann so:

test=*# select * from conan ;
  datum   
------------
 2016-01-01
 2016-03-10
 2016-07-15
 2016-11-25
(4 rows)

test=*# select date_part('month', d), count(conan.*) from generate_series('2016-01-01', '2016-12-01','1 month'::interval) d left join conan on date_part('month', d) = date_part('month', conan.datum) group by 1;
 date_part | count
-----------+-------
  1 |  1
  2 |  0
  3 |  1
  4 |  0
  5 |  0
  6 |  0
  7 |  1
  8 |  0
  9 |  0
  10 |  0
  11 |  1
  12 |  0
(12 rows)

Ich seh grad: man könnte das noch einfacher machen und einfach nur 1-12 generieren:

test=*# select d.d, count(conan.*) from generate_series(1,12) d left join conan on d.d = date_part('month', conan.datum) group by 1 order by 1;
 d  | count
----+-------
  1 |  1
  2 |  0
  3 |  1
  4 |  0
  5 |  0
  6 |  0
  7 |  1
  8 |  0
  9 |  0
 10 |  0
 11 |  1
 12 |  0
(12 rows)

Aufpassen mußt Du natürlich, wenn in Deiner Tabelle Daten von mehr als einem Jahr sind.

 

[Conan]

Danke, muss die Abfrage allerdings in der MS Access Datenbank erstellen. Da ist die Syntax doch etwas anders ?

 

[akretschmer]

Ja, sicher. Die generate_series - Funktion gibt es da sicherlich nicht. Ich kenne dieses Spielzeug, ähm, Access, nicht.

 

[akretschmer]

hast du es nun? Sollte trivial sein.

 

[Conan]

Nein, mit PostgreSQL kann ich nichts anfangen.

 

[akretschmer]

Hat nix mit PostgreSQL zu tun, Joins funktionieren gleich. Statt meiner generate-Funktion setzt Deine Tabelle ein.

 

[Conan]

Hier ein Bild meiner Tabelle:



Ich bin mittlerweile weiter und konnte mit der Abfrage...:

SELECT tabmonate.name, count(rvzurueckam) as anzahl
FROM tabmonate LEFT JOIN tabplanung a ON tabmonate.monatid = montH(a.rvzurueckam)
WHERE (((Year([a].[rvzurueckam])) Is Null Or (Year([a].[rvzurueckam]))="2015"))
group by tabmonate.name, tabmonate.monatid
ORDER BY tabmonate.monatid

folgenden View erstellen:


Nun würde ich gerne in Spalte 3 die Anzahl der Einträge für das Feld Datum anfügen. Wie muss ich die Abfrage anpassen ?
Wenn ich einfach count(datum) as anzahl2 einfüge erhalte ich den View:



Dies ist aber fehlerhaft, da ich für März im Feld Datum 2 Einträge habe.

 

[akretschmer]

du mußt nun die Monatstabelle 2 mal joinen, nun mit der anderen Spalte. Verwende Aliase, um zwischen den zwei gejointen Tabellen unterscheiden zu können.

 

[Conan]

Bekomme ungruppiert mit der Abfrage:

SELECT *
FROM
(tabmonate LEFT JOIN tabplanung AS a ON tabmonate.monatid = month(a.rvzurueckam))
LEFT JOIN tabplanung AS b ON tabmonate.MonatID = month(b.Datum)
WHERE (Year(a.rvzurueckam) Is Null Or Year(a.rvzurueckam)="2015")
and (year(b.datum) is null or year(b.datum) ="2015")
ORDER BY tabmonate.monatid;

den View:


Jetzt müssten allerdings für März die Spalten aus Tabelle B und für Juni die Spalten aus A aggregiert werden, damit das Ergebnis korrekt angezeigt wird. Hier komme ich nicht weiter..

 

[akretschmer]

test=*# select * from conan ;
  datum  |  datum2   
------------+------------
 2016-01-01 |
 2016-03-10 |
 2016-07-15 |
 2016-11-25 |
  | 2016-02-01
  | 2016-04-01
  | 2016-07-01
  | 2016-11-01
  | 2016-12-01
(9 rows)

test=*# select s.s, count(c1.*), count(c2.*) from (select s from generate_series(1,12)s)s left join conan c1 on s.s = date_part('month', c1.datum) left join conan c2 on s.s=date_part('month', c2.datum2) group by 1 order by 1;
 s  | count | count
----+-------+-------
  1 |  1 |  0
  2 |  0 |  1
  3 |  1 |  0
  4 |  0 |  1
  5 |  0 |  0
  6 |  0 |  0
  7 |  1 |  1
  8 |  0 |  0
  9 |  0 |  0
 10 |  0 |  0
 11 |  1 |  1
 12 |  0 |  1
(12 rows)

Du bekommst die Idee?

 

[Conan]

Ich verstehe deinen Vorschlag als:

SELECT s.monatid, Count(c1.rvzurueckam) AS anzahl1, Count(c2.datum) AS anzahl2
FROM
((select monatid from tabmonate) AS s
LEFT JOIN tabplanung AS c1 ON s.monatid = month(c1.RVzurueckAm))
LEFT JOIN tabplanung AS c2 ON s.monatid = month(c2.Datum)
WHERE (((Year([c1].[rvzurueckam])) Is Null Or (Year([c1].[rvzurueckam]))="2015") AND ((Year([c2].[datum])) Is Null Or (Year([c2].[datum]))="2015"))
GROUP BY s.monatid
ORDER BY s.monatid;

View sieht dann so aus:
Dies ist nicht korrekt...

 

[akretschmer]

so auf die schnelle: dein JOIN ist falsch.

 

[Conan]

Dann korrigier mich bitte in meinem Beispiel

 

[akretschmer]

du hast da wild (und wohl zum Teil falsch) Klammern gesetzt, ich denke, das ist die Ursache. Versuch zu verstehen, was ich Dir gezeigt habe.