SQL Summe und Berechnung

[pbfdg]

Hallo
folgendes Problem!

Programm Erklärung:
Kalkulation von Werkzeugen (Formenbau).

Tabellen (ausschnitt):

Tabelle 1:
Werkzeug
Felder:
ID;Nummer;Typ .. usw.

Tabelle 2:
Vorkalkulation
Felder:
ID;WERKZEUG_ID;Gruppe;Std;Kosten

Tabelle 3:
Nachkalkulation
Felder:
ID;WERKZEUG_ID;Gruppe;Std;Kosten


Nach der Erstellung einer Vorkalkulation(Tabelle 2) habe ich pro Werkzeug dann 31 Datensätze wobei jeder Datensatz eine Gruppe darstellt.

Nun werden nach und nach Nachkalkulations Datensätze eingegeben. Es können dann mehrere Datensätze für die verschiedenn Gruppe entstehen.


Nun möchte ich in einer SQL-Abfrage folgende Felder haben.

Felder:
Gruppe;SollStd;SollKosten;IstStd;IstKosten;Rest;Pr ozent

Mein Ansatz!

select v.gruppe, sum(v.std) as sollstd, sum(v.kosten) as sollkosten, sum(n.std) as iststd, sum(n.kosten) as istkosten, (sum(v.kosten) - sum(n.kosten)) as rest, (sum(n.kosten) / (sum(v.kosten)*100)) as prozent
from vorkal v
inner join nachkalkulation n on v.vorkal_id = n.vorkal_id
where (v.vorkal_id = werkzeug.id)
group by v.gruppe

Das Ergebnis dieser Abfrage ist leider nicht korrekt!
Wer kann mir auf die Sprünge helfen?


Gruß Peter

[Hansa]

Zitat von pbfdg:

Das Ergebnis dieser Abfrage ist leider nicht korrekt!
Wer kann mir auf die Sprünge helfen

Viele, aber es wird nicht gesagt, was inkorrekt ist. 8)

[marabu]

Hallo Peter,

bei der Einführung von Tabelle3 ist dir wohl ein Schreibfehler unterlaufen: statt WERKZEUG_ID sollte es wohl VORKAL_ID heißen. Durch die Verkettung (JOIN) zweier 1:n-Beziehungen in einem Statement werden die Summen für die Vorkalkulation um ein Vielfaches zu hoch ausfallen, weil für jedes Tupel aus Tabelle3 die Werte aus Tabelle2 erneut aufsummiert werden.

Grüße vom marabu

[pbfdg]

Hallo marabu,
du hast recht, die Summen stimmen nicht.
Für jeden Datensatz in der Nachkalkulation wird der Wert in der Vorkalkulation multipliziert.
Ich habe mal ein Bild erstellt indem die Tabellen mit Datensätzen zu sehen sind. Über den einzelnen Tabellen habe ich mal eine Erklärung geschrieben.
Hier nochmal meine SQL-Abfrage die zum Bild paßt:
select n.gruppe, sum(v.std) as sollstd, sum(v.kosten) as soll, sum(n.std) as std, sum(n.betrag) as ist, (sum(v.kosten) - sum(n.betrag)) as rest, (sum(n.betrag) / (sum(v.kosten+0.0001)*100)) as prozent
from vorkal v
inner join nachkalkulation n on v.gruppe = n.gruppe
where (v.vorkal_id = :id) and (n.vorkal_id = :id)
group by n.gruppe
order by n.gruppe



Nun die Frage wie kann ich das lösen?

Gruß Peter

[omata]

Hallo pbfdg,

vielleicht so...

SQL-Code:

SELECT v.gruppe
       SUM(v.std) AS sollstd, 
       SUM(v.kosten) AS soll
       COALESCE(n.std, 0) AS std,
       COALESCE(n.ist, 0) AS ist,
       SUM(v.kosten) - COALESCE(n.ist, 0) AS rest, 
       COALESCE(n.ist, 0) / (SUM(v.kosten+0.0001)*100) AS prozent
FROM vorkal v
LEFT JOIN (SELECT gruppe, 
                  SUM(n.std) AS std, 
                  SUM(n.betrag) AS ist
           FROM nachkalkulation
           WHERE vorkal_id = :id
           GROUP BY gruppe) n
  ON v.gruppe = n.gruppe
WHERE v.vorkal_id = :id
GROUP BY v.gruppe
ORDER BY v.gruppe

Deine komische Prozentspalte verstehe ich allerdings nicht so ganz.

Gruss
Thorsten

[pbfdg]

Hallo omata,

erst einmal DANKE für die Hilfe, aber leider bekomme ich eine Fehlermeldung (siehe Bild).
Mein SQL ist nicht so gut um das ganze zu verstehen.

Das was ich verstanden habe ist.
Hinter dem LEFT JOIN wird eine Unterabfrage erzeugt indem die Summen in der Nachkalkulation gebildet wird. Diese werden wiederum zur Verrechnung mit der Vorkalkulation herangezogen.

Die Fehlermeldung bezieht sich auf "(Select ...."

Hast Du vielleicht noch einen Ansatz?

Gruß Peter

[omata]

Hallo pbfdg,

stimmt meine Überlegung war noch nicht ganz korrekt.

Ein neuer Versuch...

SQL-Code:

SELECT v.gruppe,
       SUM(v.std) AS sollstd,
       SUM(v.kosten) AS soll,
       COALESCE(n.std, 0) AS std,
       COALESCE(n.ist, 0) AS ist,
       SUM(v.kosten) - COALESCE(n.ist, 0) AS rest,
       (100.00 * COALESCE(n.ist, 0)) / SUM(v.kosten) AS prozent
FROM vorkal v
LEFT JOIN (SELECT gruppe,
                  SUM(std) AS std,
                  SUM(kosten) AS ist
           FROM nachkalkulation
           WHERE vorkal_id = :id
           GROUP BY gruppe) n
  ON v.gruppe = n.gruppe
WHERE v.vorkal_id = :id
GROUP BY v.gruppe, n.std, n.ist
ORDER BY v.gruppe

Gruss
Thorsten

[pbfdg]

Hallo omata,

ich habe die SQL-Sequenz mal durch den SQL-Manager laufen lassen und folgende Fehlermeldung erhalten.
(siehe Bild)

Wenn ich meine Schriftlichen unterlagen zu rate ziehe sollte deine SQL-Sequenz eigentlich korrekt sein.


Gruß Peter

[omata]

Vielleicht liegt es ja an deiner Firebird-Version. Mit 2.0 geht die Abfrage wunderbar.

[mkinzler]

Zitat von omata:

Vielleicht liegt es ja an deiner Firebird-Version. Mit 2.0 geht die Abfrage wunderbar.

Da die Abfarge ja das neue Feature der "Derived Tables" nutzt, hierbei wird eine Abfrage wie eine Tabelle verwendet.