Abfrage summierte Werte pro Monat

[shasamkab]

Hallo zusammen,
ich versuche gerade eine Abfrage zu bauen, welche mir für drei Konten pro Monat summiert den Wert anzeigt, damit ich einen Grafen bauen kann.

Beispiel:
KontoID: 6564+6571+6525 - Zeitraum:
133+234+20 - 2024-01

Ergebnis soll sein:
387 - Januar 24
558 - Februar 24
234 - März 24
etc.

Das ist meine Abfrage aber ich bekomme die Konten nicht summiert.

SELECT
Buchungen.Soll,
Konten.KontoID
FROM Konten
INNER JOIN Mandanten
ON Konten.MandantID = Mandanten.MandantID
INNER JOIN Buchungen
ON Finanzen.Konten.KontoID = Finanzen.Buchungen.KontoID
WHERE DATE_FORMAT(Buchungen.Zeitraum,'%Y-%m') < DATE_FORMAT(NOW(), '%Y-%m')
AND Konten.KontoID = '6564'
OR DATE_FORMAT(Buchungen.Zeitraum,'%Y-%m') < DATE_FORMAT(NOW(), '%Y-%m')
AND Konten.KontoID = '6571'
OR DATE_FORMAT(Buchungen.Zeitraum,'%Y-%m') < DATE_FORMAT(NOW(), '%Y-%m')
AND Konten.KontoID = '6525'

ich ein Neuling, also bitte um Rücksicht und Vorschläge, falls die Abfrage besser gestaltet werden kann.

Ergebnis bzw. das Ziel ist es zum Schluss die Werte in einen Balkendiagramm darzustellen pro Monat.

 

[Kampfgummibaerlie]

Du möchtetst die Sum-Funktion sowie die GroupBy-Funktion nutzen.

create table konten(id integer primary key, datum date default now()::date not null, buchung numeric not null);
insert into konten(id, datum, buchung) values (1, '2000-01-01'::date, 2);
insert into konten(id, datum, buchung) values (2, '2000-02-01'::date, 2);
insert into konten(id, datum, buchung) values (4, '2000-02-01'::date, 3);
insert into konten(id, datum, buchung) values (3, '2000-03-01'::date, 2);
select sum(buchung), extract(month from(datum)) as monat  from konten group by (select extract(month from(datum))), datum order by 2;

Mein Resultat bei der Abfrage siehe Anhang!

Erklärung:
1. Summe aller Wert aus dem Monat (Negative möglich)
2. die Monatszahl

 

[shasamkab]

hmm. danke aber jetzt bin ich etwas confused:

so sind meine Tabellen angelegt:

CREATE TABLE IF NOT EXISTS `Finanzen`.`Mandanten` (
`MandantName` VARCHAR(150) NOT NULL,
`MandantID` INT NOT NULL,
`MandantKurz` VARCHAR(45) NOT NULL,
PRIMARY KEY (`MandantID`))
ENGINE = InnoDB

CREATE TABLE IF NOT EXISTS `Finanzen`.`Konten` (
`KontoID` INT NOT NULL,
`Kontobezeichnung` VARCHAR(150) NOT NULL,
`MandantID` INT NOT NULL,
PRIMARY KEY (`KontoID`),
INDEX `MandantID_idx` (`MandantID` ASC),
CONSTRAINT `MandantID`
FOREIGN KEY (`MandantID`)
REFERENCES `Finanzen`.`Mandanten` (`MandantID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB

CREATE TABLE IF NOT EXISTS `Finanzen`.`Buchungen` (
`BuchungID` INT NOT NULL AUTO_INCREMENT,
`KontoID` INT NOT NULL,
`EB_Wert_Soll` DOUBLE NULL DEFAULT NULL,
`EB_Wert_Haben` DOUBLE NULL DEFAULT NULL,
`Soll` DOUBLE NULL DEFAULT NULL,
`Haben` DOUBLE NULL DEFAULT NULL,
`Kumuliert_Soll` DOUBLE NULL DEFAULT NULL,
`Kumuliert_Haben` DOUBLE NULL DEFAULT NULL,
`Saldo_Soll` DOUBLE NULL DEFAULT NULL,
`Saldo_Haben` DOUBLE NULL DEFAULT NULL,
`Wirtschaftsjahr` DATE NOT NULL,
`Zeitraum` DATE NOT NULL,
`ImportDatum` TIMESTAMP NULL DEFAULT now(),
PRIMARY KEY (`BuchungID`),
INDEX `KontoID0_idx` (`KontoID` ASC),
CONSTRAINT `KontoID0`
FOREIGN KEY (`KontoID`)
REFERENCES `Finanzen`.`QTM_Konten` (`KontoID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB

jetzt möchte ich die Konten 6564, 6571, 6525 summiert pro Monat anzeigen lassen.

 

[Kampfgummibaerlie]

einfach in Group by die Konten dazuschreiben

create table konten(id integer primary key, datum date default now()::date not null, konto integer not null, buchung numeric not null);

Werte in die Tabelle einfügeen (alle 4 erstellten Spalten)

insert into konten(id, datum, konto, buchung) values (1, '2000-01-01'::date, 1, 2);
insert into konten(id, datum, konto, buchung) values (2, '2000-02-01'::date, 1, 2);
insert into konten(id, datum, konto, buchung) values (4, '2000-02-01'::date, 1, 3);
insert into konten(id, datum, konto, buchung) values (3, '2000-03-01'::date, 2, 2);

Hier die Abfrage

select sum(buchung), konto, extract(month from(datum)) as monat  from konten
group by (select extract(month from(datum))), konto, datum order by 3;

das Resultat sieht in etwa so aus

"sum"    "konto"    "monat"
5            1          2
2            1          1
2            2          3

LG Baerlie
bei detaillierten Fragen über den Hintergrund bitte eine pN ^^

 

[BerndB]

Hier noch die vereinfachte Version:

SELECT  SUM(buchung) AS summe_buchung
, konto
, MONTH(datum) AS monat
FROM konten
GROUP BY konto, YEAR(datum), MONTH(datum)
ORDER BY monat;

Nur noch eine kleine Anmerkung:

Die Möglichkeit per (order by 3) Nr auf Felder zuzugreifen ist zwar möglich, sollte aber meiner Ansicht nach
nicht genutzt werden.
Wenn es zB. mehrere Abfragen gibt und jemand das DB Schema ändert stimmen alle Nummern nicht mehr und man muss
alle Queries prüfen, da sie sonst fehlerhafte Ergebnisse liefern.

Gruß Bernd

 

[shasamkab]

aha, so komme ich der Sache schon näher

SELECT
SUM(Buchungen.Soll) AS summe_buchung
, MONTH(Buchungen.Zeitraum) AS monat
FROM Konten
INNER JOIN Mandanten
ON Konten.MandantID = Mandanten.MandantID
INNER JOIN Buchungen
ON Finanzen.Konten.KontoID = Finanzen.Buchungen.KontoID
WHERE DATE_FORMAT(Buchungen.Zeitraum,'%Y-%m') < DATE_FORMAT(NOW(), '%Y-%m')
AND Konten.KontoID = '6564'
OR DATE_FORMAT(Buchungen.Zeitraum,'%Y-%m') < DATE_FORMAT(NOW(), '%Y-%m')
AND Konten.KontoID = '6571'
OR DATE_FORMAT(Buchungen.Zeitraum,'%Y-%m') < DATE_FORMAT(NOW(), '%Y-%m')
AND Konten.KontoID = '6525'
GROUP BY Konten.KontoID, YEAR(Buchungen.Zeitraum), MONTH(QTM_Buchungen.Zeitraum)
ORDER BY monat;

Ergebnis sieht nun so aus:





wie bekomme ich die Monate mit den Buchungen summiert?

Habe es mit DISTINCT versucht, aber weis nicht wo ich es richtig ansetzen soll.

 

[BerndB]

Ergebnis sieht nun so aus:

Das glaub ich nicht !,

in deinem Query gibt es KEINE Tabelle Finanzen, auf die du ja referenzierst.

..... FROM Konten
INNER JOIN Mandanten
ON Konten.MandantID = Mandanten.MandantID
INNER JOIN Buchungen
ON Finanzen.Konten.KontoID = Finanzen.Buchungen.KontoID

poste mal das richtige query, dann kann man dir auch helfen.

Gruß Bernd

 

[shasamkab]

hi, hier meine Tabellen wie ich diese angelegt habe. Es gibt eine Datenbank Finanzen und darin habe ich diese Tabellen angelegt mit den entsprechenden Spalten:

CREATE TABLE IF NOT EXISTS `Finanzen`.`Mandanten` (
`MandantName` VARCHAR(150) NOT NULL,
`MandantID` INT NOT NULL,
`MandantKurz` VARCHAR(45) NOT NULL,
PRIMARY KEY (`MandantID`))
ENGINE = InnoDB

CREATE TABLE IF NOT EXISTS `Finanzen`.`Konten` (
`KontoID` INT NOT NULL,
`Kontobezeichnung` VARCHAR(150) NOT NULL,
`MandantID` INT NOT NULL,
PRIMARY KEY (`KontoID`),
INDEX `MandantID_idx` (`MandantID` ASC),
CONSTRAINT `MandantID`
FOREIGN KEY (`MandantID`)
REFERENCES `Finanzen`.`Mandanten` (`MandantID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB

CREATE TABLE IF NOT EXISTS `Finanzen`.`Buchungen` (
`BuchungID` INT NOT NULL AUTO_INCREMENT,
`KontoID` INT NOT NULL,
`EB_Wert_Soll` DOUBLE NULL DEFAULT NULL,
`EB_Wert_Haben` DOUBLE NULL DEFAULT NULL,
`Soll` DOUBLE NULL DEFAULT NULL,
`Haben` DOUBLE NULL DEFAULT NULL,
`Kumuliert_Soll` DOUBLE NULL DEFAULT NULL,
`Kumuliert_Haben` DOUBLE NULL DEFAULT NULL,
`Saldo_Soll` DOUBLE NULL DEFAULT NULL,
`Saldo_Haben` DOUBLE NULL DEFAULT NULL,
`Wirtschaftsjahr` DATE NOT NULL,
`Zeitraum` DATE NOT NULL,
`ImportDatum` TIMESTAMP NULL DEFAULT now(),
PRIMARY KEY (`BuchungID`),
INDEX `KontoID0_idx` (`KontoID` ASC),
CONSTRAINT `KontoID0`
FOREIGN KEY (`KontoID`)
REFERENCES `Finanzen`.`Konten` (`KontoID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB

Dateninhalt ist denke ich selbsterklärend oder soll ich da auch was posten?


Das ist die Abfrage abgleitet wie ich es jetzt gebaut habe, nachdem ihr mir die Antworten geliefert habt.

SELECT
SUM(Buchungen.Soll) AS summe_buchung
, MONTH(Buchungen.Zeitraum) AS monat
FROM Konten
INNER JOIN Mandanten
ON Konten.MandantID = Mandanten.MandantID
INNER JOIN Buchungen
ON Finanzen.Konten.KontoID = Finanzen.Buchungen.KontoID
WHERE DATE_FORMAT(Buchungen.Zeitraum,'%Y-%m') < DATE_FORMAT(NOW(), '%Y-%m')
AND Konten.KontoID = '6564'
OR DATE_FORMAT(Buchungen.Zeitraum,'%Y-%m') < DATE_FORMAT(NOW(), '%Y-%m')
AND Konten.KontoID = '6571'
OR DATE_FORMAT(Buchungen.Zeitraum,'%Y-%m') < DATE_FORMAT(NOW(), '%Y-%m')
AND Konten.KontoID = '6525'
GROUP BY Konten.KontoID, YEAR(Buchungen.Zeitraum), MONTH(QTM_Buchungen.Zeitraum)
ORDER BY monat;

Ergebnis:

Siehe Screenshot:

 

[BerndB]

Und wo klonst du die Tabelle Finanzen ??

INNER JOIN Buchungen
ON Finanzen.Konten.KontoID = Finanzen.Buchungen.KontoID

Kann also so nicht gehen

Gruß Bernd

 

[shasamkab]

korrekt. das hatte ich mal angegeben, allerdings hat das keine Auswirkung. Wenn ich das weglasse habe ich das selbe Ergebnis wie oben.

 

[BerndB]

Hier sind 2 Möglichkeiten dies zu machen. Die summe steht immer in der letzten Zeile.
Beim 2. Beispiel wir bei Monat dann Gesamt ausgegeben-

SELECT SUM(b.Soll) AS Kosten, DATE_FORMAT(MAX(b.Zeitraum), '%Y-%m-01') AS Monat
FROM Konten k
INNER JOIN Mandanten m ON k.MandantID = m.MandantID
INNER JOIN Buchungen b ON k.KontoID = b.KontoID
WHERE b.Zeitraum < DATE_FORMAT(NOW(), '%Y-%m-01')
AND k.KontoID IN (135, 136, 500)
GROUP BY DATE_FORMAT(b.Zeitraum, '%Y-%m-01') WITH ROLLUP
ORDER BY IF(Monat IS NULL,1,0), Monat;

Kosten    Monat
33    2024-01-01
127    2024-02-01
160    2024-02-01

SELECT
    SUM(b.Soll) AS Kosten,
    CASE
        WHEN GROUPING(DATE_FORMAT(b.Zeitraum, '%Y-%m-01')) = 1 THEN 'Gesamt'
        ELSE DATE_FORMAT(MAX(b.Zeitraum), '%Y-%m-01')
    END AS Monat
FROM
    Konten k
INNER JOIN
    Mandanten m ON k.MandantID = m.MandantID
INNER JOIN
    Buchungen b ON k.KontoID = b.KontoID

WHERE
b.Zeitraum < DATE_FORMAT(NOW(), '%Y-%m-01')
AND k.KontoID IN (135, 136, 500)
GROUP BY
DATE_FORMAT(b.Zeitraum, '%Y-%m-01') WITH ROLLUP
ORDER BY
IF(Monat = 'Gesamt', 1, 0),
Monat;[/CODE]

Kosten    Monat
33    2024-01-01
127    2024-02-01
160    Gesamt

 

[shasamkab]

Beide Varianten sind sehr gut! Nice, das mit Rollup und WHEN kannte ich bisher noch nicht. Das hat mir sehr geholfen!