Entwickler-Ecke :: Thema anzeigen - Richtig Gruppiern einer Abfrage

Talemantros - Mo 30.05.16 17:59
Titel: Richtig Gruppiern einer Abfrage

Hallo zusammen,
in meiner Software habe ich sogenannte Oktabiner, diese sind Ausgangsbehälter in denen sich verarbeitete Ware befindet.
In einem Oktabiner kann entweder eine, oder mehrere verarbeitete Waren sein.

Nun möchte ich bestimmte Oktabiner in einem Dokument ausgeben.
Dies aber nicht mit jedem Oktabiner einzeln, sondern alle zusammen und in bestimmten Zeilen summiert/gezählt

Nun habe ich das Problem, dass ich z.B.: 4 Oktabiner auflisten will, da in einem Oktabiner 2 Waren sind sagt er beim count() immer Anzahl 5

Original im Quelltext:

C#-Quelltext

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:

        public static DataTable GetDataTableCollectBillIDCountCrapWeigthPrice(IEnumerable<long> oktabinerIds)
        {
            strSQl = @"Select 
                        oktabinergrund.oktabinergrundid,
                        count(oktabinergrund.oktnummer) as 'Anzahl Oktabiner',
                        schrottart.schrottart as Schrottart,
                        sum(oktabinergrund.netto) as 'Netto Gesamt',
                        schrottart.preis as 'Einzelpreis/kg',
                        sum(round((schrottart.preis * oktabinergrund.netto),2)) as Preis
                        from oktabinergrund
                        left join oktabinerinhalt on oktabinerinhalt.oktabinergrundid=oktabinergrund.oktabinergrundid
                        left join bestand on bestand.bestandid=oktabinerinhalt.bestandid
                        left join schrottart on schrottart.schrottartid=bestand.schrottartid
                        left join kunde on kunde.kundeid = schrottart.kundeid ";

            String ids = String.Join(", ", oktabinerIds);
            strSQl += string.Format(" where oktabinergrund.oktabinergrundid in ({0})", ids);
            strSQl += " group by schrottart.schrottartid";

            DataTable dt = new DataTable();

            using (MySqlConnection conn = new MySqlConnection(connStr))
            {
                using (MySqlDataAdapter da = new MySqlDataAdapter(strSQl, conn))
                {
                    da.Fill(dt);
                }
            }

            return dt;
        }

Aufgerufen mit :

C#-Quelltext

1:	dtRechnung = TemplateMethodsIntern.GetDataTableCollectBillIDCountCrapWeigthPrice(dt.AsEnumerable().Select(row => (long)row[0]));

Wenn ich die Abfrage in der HeidiSQL anwende wie folgt:

SQL-Anweisung

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:

Select 
oktabinergrund.oktabinergrundid,
oktabinergrund.oktnummer,
schrottart.schrottart,
oktabinergrund.netto as 'Netto Gesamt',
schrottart.preis as 'Einzelpreis/kg',
round((schrottart.preis * oktabinergrund.netto),2) as Preis
from oktabinergrund
left join oktabinerinhalt on oktabinerinhalt.oktabinergrundid=oktabinergrund.oktabinergrundid
left join bestand on bestand.bestandid=oktabinerinhalt.bestandid
left join schrottart on schrottart.schrottartid=bestand.schrottartid
left join kunde on kunde.kundeid = schrottart.kundeid
where oktabinergrund.oktabinergrundid in (3,4,5,6)

kommt folgendes Ergebnis raus:

Quelltext

1:
2:
3:
4:
5:
6:

oktabinergrundid  oktnummer  schrottart  Netto Gesamt  Einzelpreis/kg  Preis
3                  OKT215000003  Reine Ware  4456  2,5  11140
4                  OKT215000004  Reine Ware  2123  2,5  5307,5
4                  OKT215000004  Reine Ware  2123  2,5  5307,5
5                  OKT215000005  Reine Ware  1123  2,5  2807,5
6                  OKT215000006  Reine Ware  7778  2,5  19445

Wie ihr seht ist der OKT215000004 doppelt, da er in der Tabelle oktabinerinahlt 2 Waren aus der Tabelle bestand hat.

Nun möchte ich als Ergebnis folgendes:

Quelltext

1:
2:

oktabinergrundid  Anzahl Oktabiner  Schrottart  Netto Gesamt  Einzelpreis/kg  Preis
3                  5                  Reine Ware  17603            2,5  44007,5

DIes habe ich derzeit mit folgendem Befehl gemacht:

SQL-Anweisung

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:

Select 
oktabinergrund.oktabinergrundid,
count(oktabinergrund.oktnummer) as 'Anzahl Oktabiner',
schrottart.schrottart as Schrottart,
sum(oktabinergrund.netto) as 'Netto Gesamt',
schrottart.preis as 'Einzelpreis/kg',
sum(round((schrottart.preis * oktabinergrund.netto),2)) as Preis
from oktabinergrund
left join oktabinerinhalt on oktabinerinhalt.oktabinergrundid=oktabinergrund.oktabinergrundid
left join bestand on bestand.bestandid=oktabinerinhalt.bestandid
left join schrottart on schrottart.schrottartid=bestand.schrottartid
left join kunde on kunde.kundeid = schrottart.kundeid
where oktabinergrund.oktabinergrundid in (3,4,5,6)
group by schrottart.schrottartid

Im Ergebnis steht aber die Anzahl als 5 was derzeit falsch ist.

Habt ihr da eine Lösung für mich...vermutlich ist es wieder was völlig kleines, aber ich komme seit einiger Zeit einfach nicht drauf,

Danke

Ich hoffe ich es ist einigermaßen verständlich und ausführlich

EDIT: Kaum schreibe ich es so ausführlich für euch auf merke ich den Fehler und habe ihn mit Distinct() behoben

Moderiert von Christian S.: Source-Tags angepasst

Palladin007 - Di 31.05.16 07:07

Zitat:

EDIT: Kaum schreibe ich es so ausführlich für euch auf merke ich den Fehler und habe ihn mit Distinct() behoben

Das Prinzip kenne ich unter dem Namen Quietscheentchen-Debugging [http://quietscheentchen.com/quietscheentchen-debugging] ;)
Kannst dir ja mal so ein Quietscheentchen auf den Schreibtisch stellen oder wenn Du so ein Thema schreibst, vor dem Abschicken noch zwei/drei mal durchlesen ^^

Talemantros - Di 31.05.16 18:24

Hi,
jetzt habe ich da noch ein wenig mit rum getestet und merke es ist doch falsch... :-(

Wenn ich folgende Abfrage

SQL-Anweisung

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:

Select 
oktabinergrund.oktabinergrundid,
oktabinergrund.oktnummer as 'Anzahl Oktabiner',
schrottart.schrottart as Schrottart,
oktabinergrund.netto as 'Netto Gesamt',
schrottart.preis as 'Einzelpreis/kg',
round((schrottart.preis * oktabinergrund.netto),2) as Preis
from oktabinergrund
left join oktabinerinhalt on oktabinerinhalt.oktabinergrundid=oktabinergrund.oktabinergrundid
left join bestand on bestand.bestandid=oktabinerinhalt.bestandid
left join schrottart on schrottart.schrottartid=bestand.schrottartid
left join kunde on kunde.kundeid = schrottart.kundeid
where oktabinergrund.oktabinergrundid in (3,4,5,6)

verwende bekomme ich das Ergebnis:

Quelltext

1:
2:
3:
4:
5:
6:

oktabinergrundid  Anzahl Oktabiner  Schrottart  Netto Gesamt  Einzelpreis/kg  Preis
3                  OKT215000003          Reine Ware  4456  2,5  11140
4                  OKT215000004          Reine Ware  2123  2,5  5307,5
4                  OKT215000004          Reine Ware  2123  2,5  5307,5
5                  OKT215000005          Reine Ware  1123  2,5  2807,5
6                  OKT215000006          Reine Ware  7778  2,5  19445

Wie gesagt wäre die ID 4 doppelt.

Wenn ich nun ein Distinct() setze auf die Oktabinergrundid werden mir noch 4 angezeigt

SQL-Anweisung

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:

Select 
distinct(oktabinergrund.oktabinergrundid),
oktabinergrund.oktnummer as 'Anzahl Oktabiner',
schrottart.schrottart as Schrottart,
oktabinergrund.netto as 'Netto Gesamt',
schrottart.preis as 'Einzelpreis/kg',
round((schrottart.preis * oktabinergrund.netto),2) as Preis
from oktabinergrund
left join oktabinerinhalt on oktabinerinhalt.oktabinergrundid=oktabinergrund.oktabinergrundid
left join bestand on bestand.bestandid=oktabinerinhalt.bestandid
left join schrottart on schrottart.schrottartid=bestand.schrottartid
left join kunde on kunde.kundeid = schrottart.kundeid
where oktabinergrund.oktabinergrundid in (3,4,5,6)

Quelltext

1:
2:
3:
4:
5:

oktabinergrundid  Anzahl Oktabiner  Schrottart  Netto Gesamt  Einzelpreis/kg  Preis
3                  OKT215000003          Reine Ware  4456  2,5  11140
4                  OKT215000004          Reine Ware  2123  2,5  5307,5
5                  OKT215000005          Reine Ware  1123  2,5  2807,5
6                  OKT215000006          Reine Ware  7778  2,5  19445

Wenn ich das nun als eine Zeile angezeigt bekommen mag und Netto, Anzahl, und Preis Gesamt berechnet werden soll nimmt er statt der angezeigten 4 Einträge wieder 5 und berechnet die ID 4 doppelt

SQL-Anweisung

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:

Select 
distinct(oktabinergrund.oktabinergrundid),
count(oktabinergrund.oktnummer) as 'Anzahl Oktabiner',
schrottart.schrottart as Schrottart,
sum(oktabinergrund.netto) as 'Netto Gesamt',
schrottart.preis as 'Einzelpreis/kg',
sum(round((schrottart.preis * oktabinergrund.netto),2)) as Preis
from oktabinergrund
left join oktabinerinhalt on oktabinerinhalt.oktabinergrundid=oktabinergrund.oktabinergrundid
left join bestand on bestand.bestandid=oktabinerinhalt.bestandid
left join schrottart on schrottart.schrottartid=bestand.schrottartid
left join kunde on kunde.kundeid = schrottart.kundeid
where oktabinergrund.oktabinergrundid in (3,4,5,6)

Ergebnis:

Quelltext

1:
2:

oktabinergrundid  Anzahl Oktabiner  Schrottart  Netto Gesamt  Einzelpreis/kg  Preis
3                  5                  Reine Ware  17603          2,5          44007,5

Richtiges netto wären 15480, entsprechend mit anderem Gesamtpreis

Moderiert von Christian S.: Tags korrigiert

Ralf Jansen - Di 31.05.16 20:01

Dss 2.te und 3.te Sql Statement sind identisch da ist irgendwas schief gelaufen so hattest du das sicher nicht vor uns zu zeigen.

Talemantros - Di 31.05.16 20:32

Hey.
Die unterscheiden sich im in Zeile 3,5 und 7 wo die Summe und Anzahl aufgeführt wird.

Ralf Jansen - Di 31.05.16 20:44

Dann verstehe ich zu wenig von dem Syntax deiner Datenbank.

In allen System die ich kenne muss man zwingen ein group by angeben wenn man Aggregatfunktionen (sum, count etc.) und nicht aggregierte Spalten in der Select Klausel mischt.
Und man muss alle Spalten im group by benennen die nicht aggregiert werden. Was ist jetzt wenn für einen oktabinergrundid verschiedene Schrottart(en) zurückkommen. Wird da zufällig einer ausgewählt für den einen Datensatz den du für die oktabinergrundid bekommst? Oder bekommst du doch 2 Datensätze? Bezieht sich Distinct überhaupt nur auf eine Spalte so wqie du es durch die Klammern ausdrückst oder bezieht sich distinct nicht doch eher auf alle Werte der Zeile was mehr Sinn macht?

Für mich ist dein sql (das 3.te) einfach nur ungültig. Wen deine DB das zulässt würde ich die für kaputt halten (MySql halt)

Talemantros - Di 31.05.16 21:10

Hi,
also in einem Oktabiner kann immer nur dieselbe Art einer Schrottart sein.
Wenn ich dich richtig verstehe, habe ich es jetzt mal so probiert

SQL-Anweisung

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:

Select 
distinct oktabinergrund.oktabinergrundid,
schrottart.schrottartid,
count(oktabinergrund.oktnummer) as 'Anzahl Oktabiner',
schrottart.schrottart as Schrottart,
sum(oktabinergrund.netto) as 'Netto Gesamt',
schrottart.preis as 'Einzelpreis/kg',
sum(round((schrottart.preis * oktabinergrund.netto),2)) as Preis
from oktabinergrund
left join oktabinerinhalt on oktabinerinhalt.oktabinergrundid=oktabinergrund.oktabinergrundid
left join bestand on bestand.bestandid=oktabinerinhalt.bestandid
left join schrottart on schrottart.schrottartid=bestand.schrottartid
left join kunde on kunde.kundeid = schrottart.kundeid
where oktabinergrund.oktabinergrundid in (3,4,5,6) 
group by oktabinergrund.oktabinergrundid, schrottart.schrottartid, schrottart.schrottart, schrottart.preis

Als Ergebnis bekomme ich dann

Quelltext

1:
2:
3:
4:
5:

"oktabinergrundid"  "schrottartid"  "Anzahl Oktabiner"  "Schrottart"  "Netto Gesamt"  "Einzelpreis/kg"  "Preis"
"3"  "1"  "1"  "Reine Ware"  "4456"  "2,5"  "11140,00"
"4"  "1"  "2"  "Reine Ware"  "4246"  "2,5"  "10615,00"
"5"  "1"  "1"  "Reine Ware"  "1123"  "2,5"  "2807,50"
"6"  "1"  "1"  "Reine Ware"  "7778"  "2,5"  "19445,00"

Auch hier wäre jetzt die ID 4 doppelt

Um es mal klein zu halten habe ich nur mal 2 der Tabellen verknüpft mit

SQL-Anweisung

1:
2:
3:
4:
5:
6:
7:
8:

Select
distinct oktabinerinhalt.oktabinergrundid,
sum(netto)
from
oktabinerinhalt
left join oktabinergrund on oktabinergrund.oktabinergrundid=oktabinerinhalt.oktabinergrundid
where oktabinerinhalt.oktabinergrundid in (3,4,5,6)
group by oktabinerinhalt.oktabinergrundid

Auch hier wird die ID 4 doppelt gezählt
Wenn ich das Sum und group by rausnehme zeigt er mir alle oktabinergrundid nur einmal an

Moderiert von Christian S.: C#- zu SQL-Tags geändert

Ralf Jansen - Di 31.05.16 21:46

Bei deinem ~einfachen~ sql zeig doch auch den Inhalt von oktabinergrund und oktabinerinhalt der hier relevant ist.
Dann versteht man das vermutlich.

Talemantros - Di 31.05.16 22:01

SQL-Anweisung

1:
2:
3:
4:
5:
6:
7:
8:

Select
distinct oktabinerinhalt.oktabinergrundid,
oktabinerinhalt.bestandid,
netto
from
oktabinerinhalt
left join oktabinergrund on oktabinergrund.oktabinergrundid=oktabinerinhalt.oktabinergrundid
where oktabinerinhalt.oktabinergrundid in (3,4,5,6)

Quelltext

1:
2:
3:
4:
5:
6:

"oktabinergrundid"  "bestandid"  "netto"
"3"  "4"  "4456"
"4"  "5"  "2123"
"4"  "6"  "2123"
"5"  "7"  "1123"
"6"  "8"  "7778"

Meinst du das so?

Moderiert von Christian S.: C#- zu SQL-Tags geändert

Ralf Jansen - Di 31.05.16 22:17

Nein das ist das Ergebnis einer Abfrage ich wollte den Inhalt der beiden Tabellen (einzeln). Jetzt kann ich wieder nur raten das es in oktabinerinhalt den oktabinergrundid 4 zweimal gibt und in oktabinergrund einmal.

Auch für die komplexere Abfrage, wenn wir uns die später noch angucken müssen/wollen, wäre der Inhalte der Tabellen einzeln betrachtet hilfreich. Dein Model ist ehrlich gesagt alles andere als selbsterklärend die konkreten Daten erklären da vielleicht ein wenig was. Im Zweifel können wir das dann auch eventuell im Blindflug beantworten auch wenn uns die Inhalte nix sagen.
Du hast z.B. eine Bestand und eine Inhalt Tabelle. Das sind für mich Synonyme für die gleiche Sache. Und zwar auch noch für etwas das eine Menge (Gewicht, Anzahl etc.) von irgendwas beziffert. Dafür benutzt du die aber gar nicht. Diese Info scheint direkt in oktabinergrund zu stehen.

Talemantros - Di 31.05.16 22:24

Also die ganzen Daten sind fiktiv zum Testen

Ein Oktabiner ist ein Ausgangsbehälter in dem sich Waren aus dem Bestand nach einer Verarbeitung befinden.
In einem Oktabiner können mehrere Waren des Bestands sein
Über den Bestand sehe ich von welchem Kunden diese kamen und was für eine Schrottart es ist

Ralf Jansen - Di 31.05.16 22:51

Zitat:

SQL-Anweisung

1:
2:
3:
4:
5:
6:
7:
8:

Select
distinct oktabinerinhalt.oktabinergrundid,
sum(netto)
from
oktabinerinhalt
left join oktabinergrund on oktabinergrund.oktabinergrundid=oktabinerinhalt.oktabinergrundid
where oktabinerinhalt.oktabinergrundid in (3,4,5,6)
group by oktabinerinhalt.oktabinergrundid

Für dieses sql ist das jetzt unglücklich weil du für das Ergebnis oktabinerinhalt nicht brauchst.

Aber ich habe (hoffentlicht) das eigentliche Problem jetzt vermutlich soweit verstanden. Bestand ist ein Bestand von was anderem es ist nicht der Bestand in einem Oktabiner. Du benutz die Kette über Inhalt, Bestand nur um an die Schrottart zu kommen? (Nebenbei kannst du die Schrottart nicht auch in oktabinergrund schreiben wenn da das gewicht (netto) schon drin ist?)

Ich würde die drei Tabellen oktabinerinhalt, bestand, schrottart zu einem subselect zusammenfassen und denn nur die Schrottart zurückliefern lassen wenn du nix anderes brauchst. Hier dann bereits den distinct anwenden um die Schrottart nur einmal zu bekommen. Kunde habe ich weggelassen wird scheinbar nicht gebraucht. Und ich denke mal das das auch keine left joins sind (zumindest nicht alle) und hab das weggelassen.

Etwa so (Vorsicht in Notepad geschrieben und gegen nix getestet)

SQL-Anweisung

1:
2:
3:
4:
5:
6:
7:
8:
9:

SELECT g.oktabinergrundid, COUNT(g.oktnummer), s.schrottart, SUM(g.netto), s.preis, SUM(ROUND((s.preis * g.netto),2)) 
  FROM oktabinergrund g
         JOIN (SELECT DISTINCT i.oktabinergrundid, a.schrottart, a.preis              
                 FROM oktabinerinhalt i
                        JOIN bestand b ON b.bestandid = i.bestandid
                          JOIN schrottart a ON a.schrottartid = b.schrottartid
                WHERE i.oktabinergrundid in (3,4,5,6)) s ON g.oktabinergrundid = s.oktabinergrundid
 WHERE g.oktabinergrundid IN (3,4,5,6)      
 GROUP BY g.oktabinergrundid, s.schrottart, s.preis

Talemantros - Di 31.05.16 23:11

Hallo Ralf,
ja du hast Recht, dass ich nur wegen der Schrottart über den Bestand gehe.
Die Schrottart hatte ich nicht mit aufgenommen, da ich so wenig Daten wie möglich redundant vorhalten wollte.

Dein SQL wirft mir 4 Zeilen aus, die auch richtig sind.
Davon ab, dass ich noch nicht verstehe was da passiert, bräuchte ich eine Zeile als Ergebnis.

Versuche mal zu verstehen was da passiert und gucke ob ich dann Anzahl Oktabiner das entsprechende Netto etc irgendwie summiert bekomme.
Vielen Dank schon mal bis dahin

Ralf Jansen - Di 31.05.16 23:15

Du willst eine Gesamtsumme über oktabinergrundid 3,4,5,6 und nicht je oktabinergrundid ?

Talemantros - Di 31.05.16 23:34

Ja das wäre das Ergebnis am Ende
Den SubSelct und die Verknüpfung habe ich mal auseinander genommen und mir angeschaut.
Den verstehe ich wenigstens schon mal

Ralf Jansen - Di 31.05.16 23:50

Dann nimm g.oktabinergrundid aus dem groub by und der select Liste.

Talemantros - Mi 01.06.16 00:08

Hallo Ralf,
das war auf vielerlei Weise sehr hilfreich. Habe da wieder einiges mitgenommen.
Vielen Dank für deine Hilfe.

Viele Grüße und gute Nacht