Abfrage: Laufende Summe einer Spalte 1 in Abhängigkeit einer anderen Spalte 2 (je unique Wert Spalte 2)

Base ermöglicht es Ihnen, Ihre Daten in einer Datenbank direkt mit LibreOffice zu bearbeiten.
EinsamerBaumWald
Beiträge: 31
Registriert: Fr 28. Nov 2014, 20:41

Abfrage: Laufende Summe einer Spalte 1 in Abhängigkeit einer anderen Spalte 2 (je unique Wert Spalte 2)

Beitrag von EinsamerBaumWald » Do 8. Sep 2016, 22:43

Hallo,

ich habe mir die Finger wund gesucht und konnte für folgendes Problem keine Lösung finden.
Ich habe erfolgreich eine korrelierte Unterabfrage erstellen können, die mir die fortlaufende Summierung einer Spalte ausgibt:

Code: Alles auswählen

SELECT 
   "A"."Kraut", "A"."Sacknummer", "A"."Gewicht",  
   ( SELECT SUM("B"."Gewicht") 
     From "Kräuter" as "B" 
     WHERE "B"."id" <= "A"."id" AND "verkauft" = 0 ) AS "Laufende Summe" 
From "Kräuter" as "A" 
WHERE 
"verkauft" = 0 
Abfrage: LaufendeSummierung

Jetzt möchte ich gerne die fortlaufende Summe je nach Kraut-Art (Spalte Kraut) haben. Da ich die fortlaufende Summe möchte, kann ich ja nicht mit GROUP BY "Kraut" arbeiten.

Ziel ist eine Ausgabe wie sie der folgende Code produziert, nur für alle Krautarten und dass ich diese natürlich nicht manuell vorgeben möchte:

Code: Alles auswählen

SELECT 
   "A"."Kraut", "A"."Sacknummer", "A"."Gewicht",  
   ( SELECT SUM("B"."Gewicht") 
     From "Kräuter" as "B" 
     WHERE "B"."id" <= "A"."id" AND "verkauft" = 0 AND "Kraut" ='SZr') as "Laufende Summe" 
From "Kräuter" as "A"
WHERE 
"verkauft" = 0 AND "Kraut" ='SZr'

UNION

SELECT 
   "A"."Kraut", "A"."Sacknummer", "A"."Gewicht",  
   ( SELECT SUM("B"."Gewicht") 
     From "Kräuter" as "B" 
     WHERE "B"."id" <= "A"."id" AND "verkauft" = 0 AND "Kraut" ='P') as "Laufende Summe" 
From "Kräuter" as "A"
WHERE 
"verkauft" = 0 AND "Kraut" ='P'
Abfrage: "LaufendeSummierung_m"

Nebenfrage: Kann man die Abfrage eleganter formulieren, so dass die Bedingungen für WHERE nur einmal formuliert werden müssen?

Wo will ich damit hin? Ich möchte eine Liste der Säcke abfragen, deren Gesamtgewicht einer Krautart z.B. mind 100kg entspricht. (Also, ich habe eine Bestellung reinbekommen über 100kg der Krautart D, 50kg von B, jetzt muss eine Packliste erstellt werden, welche Säcke dafür auf eine Palette gepackt werden müssen.)

Gebe ich Krautart und Bestellmenge fest vor, habe ich folgenden Befehl:

Code: Alles auswählen

SELECT 
   "A"."Kraut", "A"."Sacknummer", "A"."Gewicht",  
   ( SELECT SUM("B"."Gewicht") 
     From "Kräuter" as "B" 
     WHERE "B"."id" <= "A"."id" AND "verkauft" = 0 AND "Kraut"='D') AS "Laufende Summe"
From "Kräuter" as "A"
WHERE 
"verkauft" = 0 AND "Kraut"='D' AND "Laufende Summe" <=100


Jetzt die Frage, wie bekomme ich auch noch die nächste Zeile mit angezeigt? Weil der Kunde möchte mindestens 100kg haben, nicht weniger. Aber da ich die Liste der Säcke brauche, die 100kg ergeben, kann ich nicht mit
"Laufende Summe" >= 100 LIMIT 1
arbeiten.
Oder muss ich einmal die Abfrage damit laufen lassen und in die erste einbauen?

Code: Alles auswählen

SELECT 
   "A"."Kraut", "A"."Sacknummer", "A"."Gewicht",  
   ( SELECT SUM("B"."Gewicht") 
     From "Kräuter" as "B" 
     WHERE "B"."id" <= "A"."id" AND "verkauft" = 0 AND "Kraut"='D') AS "Laufende Summe"
From "Kräuter" as "A", (SELECT    "A"."Kraut", "A"."Sacknummer", "A"."Gewicht",     ( SELECT SUM("B"."Gewicht")      From "Kräuter" as "B"      WHERE "B"."id" <= "A"."id" AND "verkauft" = 0 AND "Kraut"='D') AS "Laufende Summe" From "Kräuter" as "A" WHERE "verkauft" = 0 AND "Kraut"='D' AND "Laufende Summe" >=100 LIMIT 1) AS "Sum_until"
WHERE 
"verkauft" = 0 AND "Kraut"='D' AND "Laufende Summe" <= "Sum_until"."Laufende Summe"
Abfrage: Sum_until2

Funktionieren würde es zumindest.

Anbei die Datenbank.


Ich würde mich über Denkanstöße und Hinweise freuen.

Vielen Dank!

Grüße,
EinsamerBaumWald
Dateianhänge
Lagerbuch_Beispiel.odb.zip
(24.64 KiB) 101-mal heruntergeladen

F3K Total
Beiträge: 1825
Registriert: So 10. Apr 2011, 10:10

Re: Abfrage: Laufende Summe einer Spalte 1 in Abhängigkeit einer anderen Spalte 2 (je unique Wert Spalte 2)

Beitrag von F3K Total » Do 8. Sep 2016, 23:35

Hi,
zur ersten Frage, vielleicht so?

Code: Alles auswählen

SELECT 
    "A"."Kraut", 
    "A"."Sacknummer", 
    "A"."Gewicht", 
    ( SELECT 
        SUM( "B"."Gewicht" ) 
        FROM 
            "Kräuter" AS "B" 
        WHERE 
            "B"."id" <= "A"."id" 
        AND 
            "B"."Kraut" = "A"."Kraut" 
        AND 
            "verkauft" = 0 ) AS "Running Total"
FROM
    "Kräuter" AS "A" 
WHERE
    "verkauft" = 0
Gruß R

EinsamerBaumWald
Beiträge: 31
Registriert: Fr 28. Nov 2014, 20:41

Re: Abfrage: Laufende Summe einer Spalte 1 in Abhängigkeit einer anderen Spalte 2 (je unique Wert Spalte 2)

Beitrag von EinsamerBaumWald » Fr 9. Sep 2016, 01:21

Hi R,

vielen Dank! *kopf klatsch*
Manchmal kommt man auf die naheliegendsten Sachen nicht…
Das ist genau, was ich gesucht habe!
Und ändere ich dann die Abfrage Sum_until2 folgendermaßen, dann bekomme ich schon eine Liste für zwei Krautarten:

Code: Alles auswählen

SELECT
    "A"."Kraut",
    "A"."Sacknummer",
    "A"."Gewicht",
    ( SELECT
        SUM( "B"."Gewicht" )
        FROM
            "Kräuter" AS "B"
        WHERE
            "B"."id" <= "A"."id"
        AND
            "B"."Kraut" = "A"."Kraut"
        AND
            "verkauft" = 0 ) AS "Laufende Summe"
FROM
    "Kräuter" AS "A",
	( SELECT 
		"A"."Kraut",
	 	"A"."Gewicht", 
		( SELECT 
			SUM( "B"."Gewicht" ) 
			FROM "Kräuter" AS "B" 
			WHERE "B"."id" <= "A"."id"
			AND "verkauft" = 0 
			AND "B"."Kraut" = "A"."Kraut" ) AS "Laufende Summe" 
	FROM "Kräuter" AS "A" 
	WHERE "Laufende Summe" >= 50
	LIMIT 1 ) AS "Sum_until" 


WHERE

	( "Kraut" = 'D' 
	 AND "Laufende Summe" <= "Sum_until"."Laufende Summe"
	)
OR
	( "Kraut" = 'M' 
	 AND "Laufende Summe" <= "Sum_until"."Laufende Summe"
	)

Jetzt möchte ich natürlich die Bestellmenge je nach Krautart ändern, also habe ich eine Tabelle Bestellung angelegt, ganz vereinfacht nur mit den Spalten id (Primärschlüssel mit Autowert), Produkt und Menge. Diese befüllt mit D, 100 und B, 50.

Dann die obige Abfrage geändert zu

Code: Alles auswählen

SELECT
    "A"."Kraut",
    "A"."Sacknummer",
    "A"."Gewicht",
    ( SELECT
        SUM( "B"."Gewicht" )
        FROM
            "Kräuter" AS "B"
        WHERE
            "B"."id" <= "A"."id"
        AND
            "B"."Kraut" = "A"."Kraut"
        AND
            "verkauft" = 0 ) AS "Laufende Summe"
FROM
    "Kräuter" AS "A",
	"Bestellung",
	( SELECT 
		"A"."Kraut",
	 	"A"."Gewicht", 
		( SELECT 
			SUM( "B"."Gewicht" ) 
			FROM "Kräuter" AS "B" 
			WHERE "B"."id" <= "A"."id"
			AND "verkauft" = 0 
			AND "B"."Kraut" = "A"."Kraut" ) AS "Laufende Summe" 
	FROM "Kräuter" AS "A"
	WHERE "Laufende Summe" >=(SELECT "Menge" FROM "Bestellung" WHERE "Produkt" = "A"."Kraut")
	LIMIT 1 ) AS "Sum_until"


WHERE
	"verkauft" = 0
AND
	( "Kraut" = "Bestellung"."Produkt" 
	 AND "Laufende Summe" <= "Sum_until"."Laufende Summe"
	)
Abfrage: Sum_until2

Und beim Ausführen der Abfrage wird mir ein Denkfehler bewusst, den ich gemacht habe: So bekomme ich auch bei Kraut D nur die Säcke bis 50kg aufgelistet… :oops:

Also muss ich das doch irgendwie anders lösen.


Vielen Dank & Gruß,
EinsamerBaumWald

PS:
Gerade bemerkt: Nur wenn SQL-Kommando direkt ausführen angehakt ist, bleibt die Formatierung der Abfrage erhalten.
Dateianhänge
Lagerbuch_Beispiel.odb.zip
Neue Tabelle Bestellung, Abfrage Sum_until2 bearbeitet
(20.66 KiB) 80-mal heruntergeladen

F3K Total
Beiträge: 1825
Registriert: So 10. Apr 2011, 10:10

Re: Abfrage: Laufende Summe einer Spalte 1 in Abhängigkeit einer anderen Spalte 2 (je unique Wert Spalte 2)

Beitrag von F3K Total » Fr 9. Sep 2016, 11:59

Hi, habe da mal drei Abfragen zusammengebaut:
1.) LaufendeSummierung

Code: Alles auswählen

SELECT
    COUNT(*) as "lfdNr",
    "A"."Kraut",
    "A"."Sacknummer",
    "A"."Gewicht",  
    SUM( "A"."Gewicht" ) AS "Laufende Summe"
FROM
    "Kräuter" AS "A"
INNER JOIN 
    "Kräuter" AS "B"
ON 
    "B"."id" <= "A"."id"
AND
    "B"."Kraut" = "A"."Kraut"
AND
    "A"."verkauft" = 0
AND
    "B"."verkauft" = 0
GROUP BY
    "A"."Kraut",
    "A"."Sacknummer",
    "A"."Gewicht"
2.) LetzterSack Hier wird um einen Sack weitergezählt ...

Code: Alles auswählen

SELECT
    "Produkt",
    MAX( "lfdNr" ) + 1 AS "LetzterSack"
FROM
    "LaufendeSummierung",
    "Bestellung"
WHERE
    "Kraut" = "Produkt"
AND
    "Laufende Summe" <= "Menge"
GROUP BY
    "Produkt"
3.) Saecke_fuer_Bestellungen

Code: Alles auswählen

SELECT 
    "Kraut", 
    "Sacknummer", 
    "Gewicht", 
    "Laufende Summe"
FROM
    "LaufendeSummierung",
    "LetzterSack"
WHERE 
    "Kraut" = "Produkt"
and
    "lfdNr" <= "LetzterSack"
Du kannst Sie natürlich zu einer Abfrage zusammenbauen...
Ergebnis der Abfrage Saecke_fuer_Bestellungen ist:

Code: Alles auswählen

Kraut  Sacknummer  Gewicht   Laufende Summe
B      14          5,4       5,4
B      15          6,3       12,6
B      17          5,9       17,7
B      18          6,3       25,2
B      19          5,5       27,5
B      20          6,1       36,6
B      21          5,9       41,3
B      22          6         48
B      23          5,9       53,1
D      1           8         8
D      2           8         16
D      3           8,1       24,3
D      4           8,1       32,4
D      5           8,1       40,5
D      6           8,1       48,6
D      7           8,3       58,1
D      8           7,9       63,2
D      9           8,5       76,5
D      10          7,6       76
D      11          8,3       91,3
D      12          7,9       94,8
D      13          7,9       102,7
Das läßt sich sicher noch vereinfachen, wer eine Idee hat, möge sich melden.
Gruß R

EinsamerBaumWald
Beiträge: 31
Registriert: Fr 28. Nov 2014, 20:41

Re: Abfrage: Laufende Summe einer Spalte 1 in Abhängigkeit einer anderen Spalte 2 (je unique Wert Spalte 2)

Beitrag von EinsamerBaumWald » Fr 9. Sep 2016, 13:22

Hi R,

großartig, vielen Dank!

Jetzt habe ich nur noch eine Frage: Selbst nach einfügen der Primarschlüssel kann ich keine Datensätze in den Abfragen bearbeiten - wie kann das gelöst werden?

Ich möchte ja dann die Tabelle Kräuter aktualisieren:

Code: Alles auswählen

UPDATE "Kräuter" SET "Kunde" = 'Muster Kunde' , "Verkaufsdatum" =  {d '2016-09-09' } WHERE "Kräuter"."id"="Saecke_fuer_Bestellungen"."id" 
Dies funktioniert natürlich nicht, weil die Datenbank die Abfrage Saecke_fuer_Bestellungen nicht kennt. Die Abfragen als Views zu realisieren funktioniert, aber im View bekomme ich die Spalte "id" nicht angezeigt??

Vielen Dank!
Gruß,
EinsamerBaumWald

F3K Total
Beiträge: 1825
Registriert: So 10. Apr 2011, 10:10

Re: Abfrage: Laufende Summe einer Spalte 1 in Abhängigkeit einer anderen Spalte 2 (je unique Wert Spalte 2)

Beitrag von F3K Total » Fr 9. Sep 2016, 18:47

Hi,
habe in der angehängten Datei die Ansichten erzeugt, sie enthalten jetzt die id, den Kunden und das Verkaufsdatum aus der Tabelle Bestellung.
Mit folgendem Befehl klappt es dann, wenn nur eine gerade zu bearbeitende Bestellung keinen Haken bei gebucht hat:

Code: Alles auswählen

UPDATE
    "Kräuter"
SET 
    "verkauft" = true,
    "Kunde" = (Select Distinct "Kunde" from "Saecke_fuer_Bestellungen") ,
    "Verkaufsdatum" =  (Select Distinct "Verkaufsdatum" from "Saecke_fuer_Bestellungen")
WHERE
    "Kräuter"."id" IN (Select "id" from "Saecke_fuer_Bestellungen")
Gruß und viel Erfolg
R
Dateianhänge
Lagerbuch_Beispiel.zip
(21.79 KiB) 75-mal heruntergeladen

EinsamerBaumWald
Beiträge: 31
Registriert: Fr 28. Nov 2014, 20:41

Re: Abfrage: Laufende Summe einer Spalte 1 in Abhängigkeit einer anderen Spalte 2 (je unique Wert Spalte 2)

Beitrag von EinsamerBaumWald » Sa 10. Sep 2016, 00:28

Hi R,

vielen Dank! Das funktioniert perfekt.

Nur ein kleines Problem: Der Code von LaufendeSummierung ist leider fehlerhaft:
Führe bitte

Code: Alles auswählen

UPDATE "Kräuter" SET "verkauft" = false, "Kunde" = NULL ,  "Verkaufsdatum" = NULL WHERE "id" = 97 OR "id"=95 OR "id"=93
aus und öffne dann die LaufendeSummierung:
Ab da, wo ein Sack bereits verkauft ist, ist die Summierung komplett falsch. Woran liegt das?

Vielen Dank,
EinsamerBaumWald

F3K Total
Beiträge: 1825
Registriert: So 10. Apr 2011, 10:10

Re: Abfrage: Laufende Summe einer Spalte 1 in Abhängigkeit einer anderen Spalte 2 (je unique Wert Spalte 2)

Beitrag von F3K Total » Sa 10. Sep 2016, 00:46

Ups,
da hast Du Recht, ersetze

Code: Alles auswählen

SUM( "A"."Gewicht" ) AS "Laufende Summe"
durch

Code: Alles auswählen

SUM( "B"."Gewicht" ) AS "Laufende Summe"
Anbei deine Datei, mit einem zusätzlichen Formular Bestellungen, dass dir die Sache etwas erleichtern soll.
An die Schaltfläche nach Kräuter übertragen ist das Makro S_transfer_to_Kraeuter gebunden, das die notwendigen SQL-Befehle zur Übertragung in die Tabelle Kräuter direkt ausführt.
Gruß R
Dateianhänge
Lagerbuch_Beispiel.zip
(28.32 KiB) 78-mal heruntergeladen

EinsamerBaumWald
Beiträge: 31
Registriert: Fr 28. Nov 2014, 20:41

Re: Abfrage: Laufende Summe einer Spalte 1 in Abhängigkeit einer anderen Spalte 2 (je unique Wert Spalte 2)

Beitrag von EinsamerBaumWald » Sa 10. Sep 2016, 19:01

Hi Rik,

vielen Dank!
War wohl zu spät gestern, den Fehler hätte ich selbst finden können.

Ich hab nochmal meinen aktuellen Stand hochgeladen:
Es gibt eine Tabelle Bestellungen, die Bestelldatum, Kunde, Lieferdatum und den Status verwaltet und eine Tabelle Bestellung, die die Produkte und Mengen der Bestellungen enthält.
Kleine Änderungen mancher Bezeichnungen gegenüber der früheren Beispieldatei…

Jetzt können alle Kräuter einer Bestellung auf einmal übertragen werden.

Weiterhin gilt: Nur eine Bestellung darf in der Spalte "bearbeiten" WAHR sein, sonst funktioniert es nicht.
(Kann man so eine Regel nicht auch fest in der Datenbank hinterlegen?
Auch sinnvoll wäre festlegen zu können, dass in der Tabelle Bestellung die Werte der Spalte Produkt je Bestell_ID unique sein müssen.)

Jetzt habe ich noch zwei kleinere Unschönheiten gefunden; bei Bestellungmengen weniger als 1 Sackgewicht und nicht mehr als 1 Sack: In erstem Fall wird kein Sack ausgegeben, in zweitem Fall zwei Säcke, obwohl einer genug wäre.
Siehe das Beispiel der aktuellen Bestellung im Formular Bestellen.

Das wäre nicht ganz so tragisch, wenn die Abfrage Sackliste_für_Bestellung_ (ehemals Saecke_fuer_Bestellungen) editierbar wäre. Aber leider ist schon die von Dir konstruierte Abfrage LaufendeSummierung_ nicht editierbar, auf der die Sackliste beruht.

Gerne würde ich die Sackliste nämlich so erweitern, dass ich eine Spalte hinzufüge, zum Ankreuzen, welche Säcke ich von dem Vorschlag tatsächlich übernehmen möchte. Manchmal gibt es ja auch noch andere Kriterien, die ich berücksichtigen muss.
Dazu könnte man den Inhalt der Spalte "Sackliste_für_Bestellung_"."id" in eine neue Tabelle kopieren. Diese Tabelle enthält sonst nur eine solche Auswahlspalte (Ja/Nein-Feld). Und dann nehme ich diese Tabelle bzw. eine Abfrage, die anhand der id die nötigen Infos aus der Tabelle Kräuter holt, als Datengrundlage für das Formular.
Ist dieser Denkansatz richtig?

Vielen Dank!

EinsamerBaumWald
Dateianhänge
Lagerbuch.odb.zip
(42.74 KiB) 71-mal heruntergeladen

F3K Total
Beiträge: 1825
Registriert: So 10. Apr 2011, 10:10

Re: Abfrage: Laufende Summe einer Spalte 1 in Abhängigkeit einer anderen Spalte 2 (je unique Wert Spalte 2)

Beitrag von F3K Total » Sa 10. Sep 2016, 22:26

Hi,
das erste Problem kannst Du so abfangen:

Code: Alles auswählen

Casewhen( "Laufende Summe" = "Menge", MAX( "lfdNr"), MAX( "lfdNr" ) + 1 ) AS "LetzterSack"
Das zweite nicht, denn es ist klar, durch

Code: Alles auswählen

"Laufende Summe" <= "Menge"
kann dann, wenn die Menge kleiner als die kleinste Laufende Summe ist, kein Datensatz angezeigt werden. Da musst du dir was einfallen lassen, oder die Menge mindestens auf das minimale Sackgewicht erhöhen.
Gruß R

Antworten