MySQL - SUM-Funktion verschachteln

Rocket

Neuer Benutzer
Beiträge
2
Guten Abend liebe Community,

ich tüftel nun schon Stunden an einer Datenbankabfrage rum, komme aber leider nicht weiter. Daher hoffe ich, dass Ihr mir hier vielleicht einen Tipp geben könntet.

Grundsätzlich soll das ein kleiner Onlineshop werden, in dem bestimmte Pakete begrenzt erworben werden können. Dafür habe ich ganz grob 2 Tabellen:
  • Packages (PackageID, Name, Limit) --> Enthält alle verfügbaren Pakete
  • Orders (OrderID, PackageID, Amount) --> Enthält alle Bestellungen
Ich möchte, dass mir am Ende alle Paketnamen angezeigt werden, bei denen das Limit noch größer ist, als die Summe der bereits erworbenen Pakete. Bei mir rechnet er in der 2. SELECT-Anweisung die Summe von allen Datensätzen in der Spalte Amount aus und vergleicht diesen Wert mit dem Limit der einzelnen Pakete. Das System soll aber für jedes Paket diese Werte vergleichen. Ich habe mein Glück auch mal mit GROUP BY probiert, was auch nicht so recht geklappt hat. Habt ihr da vielleicht einen Rat oder ggf. einen besseren Lösungsvorschlag?

Hier mal mein (eher übersichtlicher) Versuch:
Code:
SELECT Name FROM Packages
WHERE Packages.Limit > (SELECT SUM(Amount) As Total FROM Orders, Packages
                        WHERE Packages.PackageID = Orders.PackageID)
 
Werbung:
Code:
bdrdemo=# create table packages (id bigserial primary key, name text, lim int);
CREATE TABLE
bdrdemo=# create table orders(id bigserial primary key, p_id bigint references packages, amount int);
CREATE TABLE
bdrdemo=# with new_id as (insert into packages (name, lim) values ('package 1', 10) returning id) insert into orders (p_id, amount) select * from new_id  cross join (select random()*10 from generate_series(1,4) s) x;
INSERT 0 4
bdrdemo=# with new_id as (insert into packages (name, lim) values ('package 2', 30) returning id) insert into orders (p_id, amount) select * from new_id  cross join (select random()*10 from generate_series(1,2) s) x;
INSERT 0 2
bdrdemo=# select * from packages;
         id          |   name    | lim
---------------------+-----------+-----
 1737102564375150593 | package 1 |  10
 1737102637121159170 | package 2 |  30
(2 rows)

bdrdemo=# select * from orders;
         id          |        p_id         | amount
---------------------+---------------------+--------
 1737102564391927809 | 1737102564375150593 |      1
 1737102564391927810 | 1737102564375150593 |      7
 1737102564391927811 | 1737102564375150593 |      7
 1737102564391927812 | 1737102564375150593 |      6
 1737102637121159173 | 1737102637121159170 |      7
 1737102637121159174 | 1737102637121159170 |      8
(6 rows)

bdrdemo=#
bdrdemo=#
bdrdemo=# select p.id, p.name, p.lim, sum(o.amount) from packages p left join orders o on p.id=o.p_id group by p.id, p.name, p.lim;
         id          |   name    | lim | sum
---------------------+-----------+-----+-----
 1737102564375150593 | package 1 |  10 |  21
 1737102637121159170 | package 2 |  30 |  15
(2 rows)

bdrdemo=# select p.id, p.name, p.lim, sum(o.amount) from packages p left join orders o on p.id=o.p_id group by p.id, p.name, p.lim having sum(o.amount) < p.lim;
         id          |   name    | lim | sum
---------------------+-----------+-----+-----
 1737102637121159170 | package 2 |  30 |  15
(1 row)

bdrdemo=#

so vielleicht?
 
btw, fall sich jemand über diese komischen id's wundert: das sind TIMESHARD-Werte, die nebenbei noch den Timestamp des Inserts sowie die Node-ID beinhalten und kommen von BDR, der Multi-Master-Replikation von PostgreSQL:

Code:
bdrdemo=# select *, bdr.extract_timestamp_from_timeshard(id), bdr.extract_nodeid_from_timeshard(id) from packages;
         id          |   name    | lim | extract_timestamp_from_timeshard | extract_nodeid_from_timeshard
---------------------+-----------+-----+----------------------------------+-------------------------------
 1737102564375150593 | package 1 |  10 | 2020-01-18 09:18:40.922+01       |                             1
 1737102637121159170 | package 2 |  30 | 2020-01-18 09:18:45.258+01       |                             1
(2 rows)
 
Werbung:
Guten Morgen akretschmer,

vielen Dank für deine ausführliche und schnelle Hilfe. Du hast das wirklich gut erklärt, sodass ich glaube die Abfrage verstanden zu haben. Ich wollte die ganze Zeit zwei SELECT-Anweisungen miteinander verschachteln. Deine Lösung ist dabei deutlich besser.

Ich habe noch eine Zeile in der Abfrage ergänzt. Wenn ich nämlich ein neues Paket hinzufügen würde, welches noch keiner erworben hat, dann würde es nicht angezeigt werde, da SUM(Amount) den Wert NULL zurückgeben würde. Alternativ könnte man bestimmt auch mit IFNULL() arbeiten.

Code:
SELECT P.PackageID, P.Name, P.Limit, SUM(O.Amount) As Total
FROM Packages P
LEFT JOIN Orders O
ON P.PackageID = O.PackageID
GROUP BY P.PackageID, P.Name, P.Limit
HAVING SUM(O.Amount) < P.Limit
OR SUM(O.Amount) IS NULL

Vielen Dank noch einmal :)

Gruß Rocket
 
Zurück
Oben