sum() über mehrere Tabellen

[newsletter]

Moin,

Meine Datenbank sieht so aus:

invoice_sql.jpeg

invoice:

id	client_id	vat	credit
12	2150	7.7	10

invoice_item:

id	invoice_id	price
1	12	500
2	12	100

payment_received:

id	invoice_id	amount
1	12	200
2	12	150

Nun möchte ich folgendes haben:
SELECT
SUM(invoice_item.price)
SUM(payment_received.amount)
invoice.vat
invoice.credit

Wenn ich nun 2 LEFT JOIN's mache, werden die Abfragen SUM(payment_received.amount) 2x abgefragt, die Abfrage SUM(invoice_item.price) 3x, weil durch die JOINS das mehrmals durchlaufen wird:

SELECT
invoice.id,
invoice.vat,
invoice.credit,
invoice_item.invoice_id,
SUM(invoice_item.price),
SUM(payment_received.amount)
FROM invoice
LEFT JOIN invoice_item ON invoice_item.invoice_id = invoice.id
LEFT JOIN payment_received ON payment_received.invoice_id = invoice.id
WHERE invoice.id = 12

Wie kann ich 3 Tabellen miteinander verbinden, sodass es 1x ausgeführt wird?
Geht das überhaupt ohne mehrere SELECT?

Gruss newsletter

[protestix]

ja.

[newsletter]

ja.

Geht das mit 3 Inner Joins für alle 3 Tabellen?

[akretschmer]

Moin,

Meine Datenbank sieht so aus:


Wie kann ich 3 Tabellen miteinander verbinden, sodass es 1x ausgeführt wird?
Geht das überhaupt ohne mehrere SELECT?

Gruss newsletter

Mit einer funktionierenden Datenbank würdest Du mit Deinen Tabellen eine Fehlermeldung bekommen:

edb=*> SELECT
edb-*> invoice.id,
edb-*> invoice.vat,
edb-*> invoice.credit,
edb-*> invoice_item.invoice_id,
edb-*> SUM(invoice_item.price),
edb-*> SUM(payment_received.amount)
edb-*> FROM invoice
edb-*> LEFT JOIN invoice_item ON invoice_item.invoice_id = invoice.id
edb-*> LEFT JOIN payment_received ON payment_received.invoice_id = invoice.id
edb-*> WHERE invoice.id = 12;
FEHLER:  Spalte »invoice.id« muss in der GROUP-BY-Klausel erscheinen oder in einer Aggregatfunktion verwendet werden
LINE 2: invoice.id,
        ^

Merke: alle Spalten, wenn eine Aggregation im Spiel ist (hier sum()) müssen entweder aggregiert oder gruppiert werden. MySQL erkennt den Fehler nicht und liefert ein Zufallsergebniss - meist falsch.

edb=*> select i.id, i.vat, i.credit, sum(ii.price), sum(p.amount) from invoice i left join invoice_item ii on ii.invoice_id=i.id left join payment_received p on ii.invoice_id=p.id group by 1,2,3;
 id | vat | credit | sum | sum
----+-----+--------+-----+-----
 12 | 7.7 |     10 | 600 |    
(1 row)

edb=*>

Edit: Abfrage ist falsch, siehe erc

tl;dr

wird MySQL weg.

[erc]

Geht das überhaupt ohne mehrere SELECT?

Nein, da die Summen zwingend getrennt gebildet werden müssen. Das geht in einem Query, aber nicht mit einem SELECT.

SELECT
    invoice.id,
    SUM(invoice_item.amount) AS invoice_amount,
    payment_received_sum.payment_received_amount
FROM
    invoice INNER JOIN
    invoice_item ON (...) LEFT JOIN
    (
        SELECT
            payment_received.invoice_id,
            SUM(payment_received.amount) AS payment_received_amount
        FROM
            payment_received
        GROUP BY
            payment_received.invoice_id
    ) AS payment_received_sum ON (...)

PS: der Query ist sehr inperformant weil payment_received komplett aufsummiert wird. Dort ggf. auch mit das WHERE rein ziehen und um das zu begrenzen. Die Alternative sind CTE wenn du Mysql 8 hast.

PSS: wenn du das oft brauchst, kann es sich anbieten, die Summen zusätzlich in invoice zu speichern. (müssen dann aber halt konsistent gehalten werden...)

[newsletter]

Hallo,

danke für eure Hilfe.
Meine SQL-Kenntnisse sind nicht so gut, versuche aber mein bestes....

SELECT
    invoice.id,
    SUM(invoice_item.price) AS invoice_price,
    payment_received_sum.payment_received_amount,
    payment_received.invoice_id
FROM
    invoice INNER JOIN
    invoice_item ON invoice_item.invoice_id = invoice.id LEFT JOIN
    (
        SELECT
            payment_received.invoice_id,
            SUM(payment_received.amount) AS payment_received_amount
        FROM
            payment_received
        WHERE payment_received.invoice_id = 20124
        GROUP BY
            payment_received.invoice_id
    ) AS payment_received_sum ON payment_received.invoice_id = invoice.id
    WHERE invoice.id = 20124

Der innere Block funktioniert wie erwartet super:

SELECT
            payment_received.invoice_id,
            SUM(payment_received.amount) AS payment_received_amount
        FROM
            payment_received
        WHERE payment_received.invoice_id = 20124
        GROUP BY
            payment_received.invoice_id

Dementsprechend ist

payment_received_amount (= richtiges Ergebnis für amount) = payment_received_sum

Also:

SELECT
    invoice.id,
    SUM(invoice_item.price) AS invoice_price,
    payment_received_sum.payment_received_amount,
    payment_received.invoice_id
FROM
    invoice INNER JOIN
    invoice_item ON invoice_item.invoice_id = invoice.id LEFT JOIN
    (
payment_received_amount /* richtiges Ergebnis für payment_received.amount*/
) AS payment_received_sum ON payment_received.invoice_id = invoice.id
    WHERE invoice.id = 20124

er sagt mir hier dass payment_received.invoice_id unbekannt ist (#1054 - Unbekanntes Tabellenfeld 'payment_received.invoice_id' in field list) , aber den habe ich doch im obersten SELECT (ausserhalb der Klammern) zusätzlich deklariert...

Ich begreife das hier nicht:

) AS payment_received_sum ON payment_received.invoice_id = invoice.id

Hier sage ich, dass payment_received_amount = payment_received_sum ist. Wofür ist denn das ON? ich kann doch keine Verknüpfung machen von einem Wert zu einer Id oder sowas?
Nach dem ON wäre "payment_received_sum.? = ?.?" ?

payment_received_sum.payment_received_amount,

sehe ich zum ersten mal...

Habe es nochmal versucht zum neu zu machen aber das gibt nur bullshit

SELECT
    invoice.id,
    SUM(invoice_item.price) AS invoice_item_price
FROM
    invoice
LEFT JOIN invoice_item ON invoice_item.invoice_id = invoice.id
LEFT JOIN
(
    SELECT
    payment_received.invoice_id,
    SUM(payment_received.amount) AS payment_received_amount
    FROM
    payment_received
    WHERE payment_received.invoice_id = 20124
    ORDER BY payment_received.invoice_id
)
WHERE invoice.id = 20124
/* ORDER BY...*/

Habe es auch versucht zu zeichnen mit diesen Kreisen, aber

 invoice INNER JOIN     invoice_item ON (...) LEFT JOIN     (

da ists ein LEFT JOIN innerhalb eines INNER JOIN müsste da kein UNION ALL oder so hin?

Sorry, deine Hilfe ist sicher mehr als gut aber ich check's nicht

(Edit:// Ps: ich teste das immer mit dem SQL-Tab im phpmyadmin, gibts da auch was besseres zum solche sachen zu lernen?)...)

[erc]

Dieser innere Block nennt sich Subquery. Wenn der in der FROM Klausel vorkommt, kannst du dir das Ergebnis als eigenständige Tabelle vorstellen.

     (
        SELECT
            payment_received.invoice_id,
            SUM(payment_received.amount) AS payment_received_amount
        FROM
            payment_received
        WHERE payment_received.invoice_id = 20124
        GROUP BY
            payment_received.invoice_id
    ) AS payment_received_sum

Das wäre also die "Tabelle" payment_received_sum und als Inhalt hat die das Ergebnis des SELECTs. Der Query sieht im Prinzip also so aus:

invoice INNER JOIN
invoice_item ON (...) LEFT JOIN
payment_received_sum ON (...) 

Was muss also in der zweiten ON Klausel stehen? Da muss die Bezeihung zwischen invoice und payment_received_sum rein. (Stell dir ggf. vor du willst nicht nur eine Rechnung, sondern alle Rechnung aus Zeitraum X.)

[jspit]

Verstehe nicht warum das so kompliziert sein muss und hier mehrere Join's notwendig sein sollen.

SELECT
  id,
  (SELECT sum(price) FROM invoice_item where invoice_id = invoice.id) AS sumprice,
  (SELECT sum(amount) FROM payment_received where invoice_id = invoice.id) AS sumamount,
  vat,
  credit
FROM
  invoice

"id"    "sumprice"    "sumamount"    "vat"    "credit"
"12"    "600"         "350"          "7,7"    "10"

[erc]

Das ist vom Prinzip das gleiche. Der Unterschied mit den correlated subqueries zu den derrivated subqueries ist O(n log n) zu O(log n). Wenn du nur eine Handvoll Rechnung anschaust, ist das egal. Wenn du mehr Rechnung betrachtest, wird das zu einem Problem. Also z.B. sowas wie "Suche alle Rechnung die nicht ausgeglichen sind".

[newsletter]

Hallo,

Danke für deine Hilfe.
Habe es nun:

SELECT
    invoice.id,
    SUM(invoice_item.price) AS invoice_price,
    payment_received_sum.payment_received_amount
FROM
    invoice
    INNER JOIN invoice_item ON invoice_item.invoice_id = invoice.id
    LEFT JOIN
    (
        SELECT
            payment_received.invoice_id,
            SUM(payment_received.amount) AS payment_received_amount
        FROM
            payment_received
        GROUP BY
            payment_received.invoice_id
        HAVING payment_received.invoice_id = 20124
    ) AS payment_received_sum ON payment_received_sum.payment_received_amount
WHERE invoice.id = 20124

Dass es ein Subquery ist, wusste ich nicht (dementsprechend auch schwierig danach zu suchen).

Wie nennt man sowas:

  payment_received_sum.payment_received_amount,

Das ist ja eigentlich eine "erstellte" tabelle durch den Subquery, welche funktioniert, weil der Subquery immer an 1. Stelle verarbeitet wird.

Ich werde das so oder so lernen (müssen), weil ich seit ca. 2Jahren an einem eigenen ERP-System in meiner Freizeit beschäftigt bin (natürlich mit Hilfsmittel wie Bootstrap, möglichst ohne Javascript).
Da habe ich ein Ziel mit sehr viel Lerninhalt

[erc]

Das ist falsch. "payment_received_sum ON payment_received_sum.payment_received_amount" macht keinen Sinn.

Lass "HAVING payment_received.invoice_id = 20124" in dem Subquery weg und schau dir an was rauskommt. Das steht da nur drin, um die Datenbank zu entlasten (die Datenbank versteht nicht von alleine, dass du nur den Wert einer bestimmten Rechnung haben willst, oder einer Auswahl von Rechnungen) Der Query muss auch so das korrekte Ergebnis liefern.

SELECT
    invoice.id,
    SUM(invoice_item.price) AS invoice_price,
    payment_received_sum.invoice_id,
    payment_received_sum.payment_received_amount
FROM
    invoice
    INNER JOIN invoice_item ON invoice_item.invoice_id = invoice.id
    LEFT JOIN
    (
        SELECT
            payment_received.invoice_id,
            SUM(payment_received.amount) AS payment_received_amount
        FROM
            payment_received
        GROUP BY
            payment_received.invoice_id
    ) AS payment_received_sum ON payment_received_sum.payment_received_amount
WHERE invoice.id = 20124

Wo liegt der Fehler?

PS: nicht die HAVING Klausel verwenden, sondern WHERE.

*spoiler* payment_received_sum ON (payment_received_sum.invoice_id = invoice.id)*spoiler*