Abfragen: Bezug auf aggregiertes Feld

[bassman]

Hallo,

ich möchte aus einer Produktdatenbank den Gesamtpreis für eine Kundenbestellung berechnen. Die Berechnung ist im Prinzip ganz einfach: Summe aus Produktmengen * Produktpreis zzgl die Bezugskosten. Ich habe folgende Abfrage erstellt (Auszug)

Select "Bestell_ID", Sum("Preis"*"Menge") AS "Umsatz "
from ....
where....
Group by "Bestell_ID"

Das Problem ist, dass ich die Bezugskosten nur 1 Mal addieren will und nicht für jedes Produkt extra. In MS ACCESS konnte man sich auf die aggregierte Spalte Umsatz beziehen und dann einfach danach die Bezugskosten addieren, ungefähr so:
SELECT Bestell_ID, Sum(Preis*Menge) AS Umsatz , [Umsatz]+[Bezugskosten] AS Gesamtumsatz
FROM ...

Geht das auch in OO? Hab's nicht hingekriegt.

Vielen Dank im Voraus für Hilfestellungen

[madmatmed]

Ich weiß nicht, wie deine Datenbank genau aussieht, aber wenn du meine Abfrage verstehst, dann kannst du die Logik extrapolieren und auf deinen Fall anwenden:

Code: Alles auswählen

SELECT 
  "Bestell_ID", SUM( "Umsatz" ) AS "Gesamtumsatz"
FROM
  (
  SELECT "Bestell_ID", SUM( "Preis" * "Menge" ) AS "Umsatz" FROM "Bestellzeile" GROUP BY "Bestell_ID"
  UNION ALL
  SELECT "ID" AS "Bestell_ID", "Bezugskosten" AS "Umsatz" FROM "Bestellkopf"
  ) 
GROUP BY
  "Bestell_ID"

[bassman]

Hallo,

vielen Dank für deine Antwort. Hab ein bisschen probieren müssen, aber jetzt klappt es und ich habs auch verstanden. Es ist nur so, dass ich gehofft habe, dass der Code so einfach ist wie bei access. Gibt es vielleicht noch eine andere Lösung? Ich geb dir mal als beispiel meine Originalabfragen in Access und Base. Der Vorteil bei Access ist, dass man diese Abfrage in der graf. Oberfläche realisieren kann. Darauf kommt es mir eigentlich an (für Schüler geeigneter!!!) Vielleicht fällt dir ja dazu noch was ein. (Aufgabenstellung war: In einem Sportverein die Summe der Mitgliedsbeiträge und die gekauften Getränke für jedes Mitglied zu addieren)

Access
SELECT Mitglieder.M_ID, Sum([Menge]*[Preis]) AS Getränke, Sum(Sportart.Beitrag) AS Beiträge, [Getränke]+[Beiträge] AS [Gesamtkosten]
FROM Sportart INNER JOIN ((Mitglieder LEFT JOIN (Getränke RIGHT JOIN kaufen ON Getränke.G_Nr = kaufen.G_Nr) ON Mitglieder.M_ID = kaufen.M_Nr) INNER JOIN betreiben ON Mitglieder.M_ID = betreiben.Mitglieds_Nr) ON Sportart.Sport_ID = betreiben.Sport_Nr
GROUP BY Mitglieder.M_ID;


Base
SELECT "M_ID", SUM( "Kosten" ) AS "Gesamtkosten" FROM ( SELECT "M_ID", SUM( "Beitrag" ) AS "Kosten" FROM "Mitglieder", "betreiben", "Sportart" WHERE "M_ID" = "Mitglieds_Nr" AND "Sport_Nr" = "Sport_ID" GROUP BY "M_ID" UNION ALL SELECT "M_ID", SUM( "Menge" * "Preis" ) AS "Kosten" FROM "kaufen", "Getränke", "Mitglieder" WHERE "G_Nr" = "G_ID" AND "M_Nr" = "M_ID" GROUP BY "M_ID" ) GROUP BY "M_ID" ORDER BY "M_ID"
Danke und Gruß

[F3K Total]

Moin,
geht sicherlich noch kürzer, allerdings wäre es gut eine Beispieldatei hochzuladen.
Ob das kostenpflichtige Access für Schüler wirklich geeigneter ist?
Grafische Benutzeroberflächen sind wenn, nur für einfache SQL-Statements geeignet.

Gruß R

[bassman]

Ja, da hast du Recht. Aber solch komplizierte Abfragen in SQL möchte ich meinen Schülern nicht zumuten. Deswegen wäre es schön, es weitestgehend in der grafischen Benutzeroberfläche zu machen. Ein kürzerer SQL Code wäre natürlich auch schön. Auf jeden Fall schicke ich mal die Datenbank mit.

Danke schon mal im Voraus

[F3K Total]

Na, wie wäre es damit:

Code: Alles auswählen

SELECT 
    "M_ID", 
    IFNULL(SUM( "Menge" * "Preis" ),0) + SUM( "Beitrag" ) AS "Gesamt"
FROM
    "Mitglieder"
    LEFT OUTER JOIN "betreiben"
        ON "M_ID" = "Mitglieds_Nr"
    LEFT OUTER JOIN "Sportart"
        ON "Sport_Nr" = "Sport_ID" 
    LEFT OUTER JOIN "kaufen" 
        ON "M_ID" = "M_Nr" 
    LEFT OUTER JOIN "Getränke" 
        ON "G_Nr" = "G_ID"  
GROUP BY "M_ID"

Habe ich grob mit der GUI erstellt, dann händisch nachgearbeitet.
Gruß R

[bassman]

Super, habs verstanden. Eigentlich hatte ich die Idee mit den 2 Summen auch schon, aber irgendwie hab ich immer eine Fehlermeldung bekommen.

Da bleiben mir noch zwei Frage:
1. Gibt es eine Dokumentation über die Funktionen in Base( z.B. IFNULL, IF, etc.)?

2. Kann ich auch nach der Summe ein anderes nicht Aggregiertes Feld addieren? (z.B. jedes Mitglied hat einen festen Grundbeitrag, den ich dann zum Gesamtbeitrag aus den Sportarten addiere)

vielen Dank

[F3K Total]

Dokumentation:
http://www.hsqldb.org -> Version 1.8.0.10
nicht agg. Feld:
Ja
Gruß R

[bassman]

Kannst du mir noch einen Tipp zu 2. geben? Habs mehrmals versucht, aber nicht hinbekommen, z.B. Summe("Beitrag")+Grundbeitrag funktioniert nicht.

Gruß

[F3K Total]

Hi,
bekomme es auf die Schnelle nicht ohne Unterabfrage hin, wenn in Mitglieder eine Spalte "Grundbeitrag" zugefügt wird, geht dies:

Code: Alles auswählen

SELECT
    "M_ID", 
    "Grundbeitrag" + "Gesamt" AS GGESAMT 
from 
(SELECT
    "M_ID","Grundbeitrag", IFNULL(SUM( "Menge" * "Preis" ),0) + IFNULL(SUM( "Beitrag" ),0)  AS "Gesamt"
FROM
    "Mitglieder"
    LEFT OUTER JOIN "betreiben"
        ON "M_ID" = "Mitglieds_Nr"
    LEFT OUTER JOIN "Sportart"
        ON "Sport_Nr" = "Sport_ID"
    LEFT OUTER JOIN "kaufen"
        ON "M_ID" = "M_Nr"
    LEFT OUTER JOIN "Getränke"
        ON "G_Nr" = "G_ID" 
GROUP BY "M_ID","Grundbeitrag")

R

[bassman]

Hi,

vielen Dank für deine Antwort. Aber es trifft es nicht ganz, was ich wollte. Mit einer Unterabfrage habe ich das auch hinbekommen, ist aber für meine Zwecke leider nicht so geeignet. Ich meinte eigentlich so eine elegante Lösung wie du sie oben schon mal vorgeschlagen hast, also am besten wieder alles in der graf. Benutzeroberfläche. Sollte dir noch dazu was einfallen, wäre ich sehr interessiert an deiner Lösung.

Grüße

[F3K Total]

So?

Code: Alles auswählen

SELECT 
"M_ID", 
SUM("Grundbeitrag")/Count(*) + IFNULL( SUM( "Menge" * "Preis" ), 0 ) + IFNULL( SUM( "Beitrag" ), 0 ) AS "Gesamt" 
FROM "Mitglieder" 
LEFT OUTER JOIN "betreiben" ON "M_ID" = "Mitglieds_Nr" 
LEFT OUTER JOIN "Sportart" ON "Sport_Nr" = "Sport_ID" 
LEFT OUTER JOIN "kaufen" ON "M_ID" = "M_Nr" 
LEFT OUTER JOIN "Getränke" ON "G_Nr" = "G_ID" 
GROUP BY "M_ID"

Ist etwas trickie, aber das Rechenergebnis stimmt.
Gruß R