SQL inkl. Aggregation und NULL

joggl

Benutzer
Beiträge
7
Hallo,
mit u. a. Tabelle möchte ich gerne eine SQL stricken, die mir aggregiert alle gleichen OU/SID/CA/CO ausgibt, mit dem höchsten Wert aus der "Mischung" von SM-Wert und UpID.

tblTest
OUSMSIDCACOUpIDBetrag
000
1​
3333000-123411111111ISET4711
10​
000
2​
3333000-123422222222ISET4711
20​
000
3​
3333000-123411111111ISET4711
30​
000
3​
3333000-123422222222ISET4711
40​
000
3​
3333000-123422222222ISET4711
50​
000
3​
3333000-1234ISET4711
60​
000
3​
3333ISET4711
70​
000
3​
333322222222ISET4711
80​

Ich habe eine Lösung für die NICHT NULL-Werte gefunden, aber diese würde ich auch benötigen...
Hier meine Lösung:
SQL:
SELECT t1.OU, t1.SID, t1.CA, t1.CO, (FORMAT(t1.SM,'00') & t1.UpID) AS LatestFC, SUM(t1.Betrag) AS [Sum Total]
FROM tblTest AS t1,
    (
        SELECT t2.OU, t2.SID, t2.CA, t2.CO, MAX(t2.LatestFCb) AS LatestFCa
        FROM
            (
            SELECT tblTest.OU, tblTest.SID, tblTest.CA, tblTest.CO, FORMAT(tblTest.SM,'00') & tblTest.UpID AS LatestFCb, Sum(tblTest.Betrag) AS [Yearly Total]
            FROM tblTest
            GROUP BY tblTest.OU, tblTest.SID, tblTest.CA, tblTest.CO, (FORMAT(tblTest.SM,'00') & tblTest.UpID)
            ORDER BY tblTest.OU, tblTest.SID, tblTest.CA, tblTest.CO
            )
            AS t2
        GROUP BY t2.OU, t2.SID, t2.CA, t2.CO
    )
    AS t3
WHERE t1.OU = t3.OU
AND t1.SID = t3.SID
AND (FORMAT(t1.SM,'00') & t1.UpID) = t3.LatestFCa
AND t1.CA = t3.CA
AND t1.CO = t3.CO
GROUP BY t1.OU, t1.SID, t1.CA, t1.CO, (FORMAT(t1.SM,'00') & t1.UpID)
ORDER BY t1.OU, t1.SID, t1.CA, t1.CO;

Erhaltenes Ergebnis:
OUSIDCACOLatestFCSum Total
0003333000-12341111111103ISET4711
30​
0003333000-12342222222203ISET4711
90​

Gewolltes Ergebnis:
OUSIDCACOLatestFCSum Total
0003333000-12341111111103ISET4711
30​
0003333000-12342222222203ISET4711
90​
0003333000-123403ISET471160
000333303ISET471170
00033332222222203ISET471180

Hätte mir jemand evtl. eine Lösung?
 
Werbung:
Du musst ANSI Joins verwenden. Und davon LEFT JOIN.
Deine jetzige Lösung mit einem Join über die WHERE Clause blendet alle NULL Werte aus.
 
Also quasi
Code:
SELECT t1.OU, t1.SID, t1.CA, t1.CO, (FORMAT(t1.SM,'00') & t1.UpID) AS LatestFC, SUM(t1.Betrag) AS [Sum Total]
FROM tblTest AS t1
 LEFT JOIN   (
        SELECT t2.OU, t2.SID, t2.CA, t2.CO, MAX(t2.LatestFCb) AS LatestFCa
        FROM
            (
            SELECT tblTest.OU, tblTest.SID, tblTest.CA, tblTest.CO, FORMAT(tblTest.SM,'00') & tblTest.UpID AS LatestFCb, Sum(tblTest.Betrag) AS [Yearly Total]
            FROM tblTest
            GROUP BY tblTest.OU, tblTest.SID, tblTest.CA, tblTest.CO, (FORMAT(tblTest.SM,'00') & tblTest.UpID)
--            ORDER BY tblTest.OU, tblTest.SID, tblTest.CA, tblTest.CO
            )
            AS t2
        GROUP BY t2.OU, t2.SID, t2.CA, t2.CO
    )
    AS t3
ON t1.OU = t3.OU
AND t1.SID = t3.SID
AND (FORMAT(t1.SM,'00') & t1.UpID) = t3.LatestFCa
AND t1.CA = t3.CA
AND t1.CO = t3.CO
GROUP BY t1.OU, t1.SID, t1.CA, t1.CO, (FORMAT(t1.SM,'00') & t1.UpID)
ORDER BY t1.OU, t1.SID, t1.CA, t1.CO;
Wobei das innere ORDER BY (hab ich auskommentiert) in MSSQL sofort einen Fehler schmeißt, ist im fertigen Statement auch überflüssig.
 
Du musst ANSI Joins verwenden. Und davon LEFT JOIN.
Deine jetzige Lösung mit einem Join über die WHERE Clause blendet alle NULL Werte aus.
Super! Danke! Sehr guter Hinweis!
Habe daraufhin exakt, noch vor dem Post von ukulele, genau das implementiert, aber kann dann noch nicht das "LatestFC"-Problem lösen, da mir hier die "Sum Total" falsch zählt... Irgendwie steh ich da auf dem Schlauch :(

Das Ergebnis wäre dann (also mein und ukulele's Bsp):



OUSIDCACOLatestFCSum Total
000333303ISET4711
70​
00033332222222203ISET4711
80​
0003333000-123403ISET4711
60​
0003333000-12341111111101ISET4711
10​
0003333000-12341111111103ISET4711
30​
0003333000-12342222222202ISET4711
20​
0003333000-12342222222203ISET4711
90​


Wobei ich dieses hier brauche:



OUSIDCACOLatestFCSum Total
000333303ISET4711
70​
00033332222222203ISET4711
80​
0003333000-123403ISET4711
60​
0003333000-12341111111103ISET4711
30​
0003333000-12342222222203ISET4711
90​
 
Also quasi
Code:
SELECT t1.OU, t1.SID, t1.CA, t1.CO, (FORMAT(t1.SM,'00') & t1.UpID) AS LatestFC, SUM(t1.Betrag) AS [Sum Total]
FROM tblTest AS t1
 LEFT JOIN   (
        SELECT t2.OU, t2.SID, t2.CA, t2.CO, MAX(t2.LatestFCb) AS LatestFCa
        FROM
            (
            SELECT tblTest.OU, tblTest.SID, tblTest.CA, tblTest.CO, FORMAT(tblTest.SM,'00') & tblTest.UpID AS LatestFCb, Sum(tblTest.Betrag) AS [Yearly Total]
            FROM tblTest
            GROUP BY tblTest.OU, tblTest.SID, tblTest.CA, tblTest.CO, (FORMAT(tblTest.SM,'00') & tblTest.UpID)
--            ORDER BY tblTest.OU, tblTest.SID, tblTest.CA, tblTest.CO
            )
            AS t2
        GROUP BY t2.OU, t2.SID, t2.CA, t2.CO
    )
    AS t3
ON t1.OU = t3.OU
AND t1.SID = t3.SID
AND (FORMAT(t1.SM,'00') & t1.UpID) = t3.LatestFCa
AND t1.CA = t3.CA
AND t1.CO = t3.CO
GROUP BY t1.OU, t1.SID, t1.CA, t1.CO, (FORMAT(t1.SM,'00') & t1.UpID)
ORDER BY t1.OU, t1.SID, t1.CA, t1.CO;
Wobei das innere ORDER BY (hab ich auskommentiert) in MSSQL sofort einen Fehler schmeißt, ist im fertigen Statement auch überflüssig.
Vielen Dank auch Dir - siehe jedoch mein voriger Post
 
Wenn in T3 wirklich nur das Maximum latestFC genommen wird, sollte SM 1 und 2 eigentlich schon im T3 Ergebnis rausfliegen (sofern die einzige übrige Varianz nur in CO liegt).
Ich bin leider im Kopfrechnen nicht mehr so gut.
 
Wenn in T3 wirklich nur das Maximum latestFC genommen wird, sollte SM 1 und 2 eigentlich schon im T3 Ergebnis rausfliegen (sofern die einzige übrige Varianz nur in CO liegt).
Ich bin leider im Kopfrechnen nicht mehr so gut.
Richtig, aber durch den LEFT JOIN (der ja für die NULL-Werte wichtig ist) hol ich sie mir letztlich "zurück"... :confused:
 
Also dein Problem war/ist das du diverse Werkzeuge nicht kennst/nutzt und dadurch sehr kompliziert vorgehst. Mein Problem war bisher das ich da überhaupt nicht durchsteige auf den ersten Blick und das deine Beispiel-Daten so abstrakt sind das es etwas schwer fällt sich da rein zu denken (Ich weiß ja nicht was das ist und hab auch die Unterschiede nicht gleich gesehen).

Ich habs mir grade nochmal im Editor angeguckt mit dem Zielergebnis und umgebaut:
Code:
WITH tblTest(OU,SM,SID,CA,CO,UpID,Betrag) AS (
    SELECT 000,1,3333,'000-1234',11111111,'ISET4711',10 UNION ALL
    SELECT 000,2,3333,'000-1234',22222222,'ISET4711',20 UNION ALL
    SELECT 000,3,3333,'000-1234',11111111,'ISET4711',30 UNION ALL
    SELECT 000,3,3333,'000-1234',22222222,'ISET4711',40 UNION ALL
    SELECT 000,3,3333,'000-1234',22222222,'ISET4711',50 UNION ALL
    SELECT 000,3,3333,'000-1234',NULL,'ISET4711',60 UNION ALL
    SELECT 000,3,3333,NULL,NULL,'ISET4711',70 UNION ALL
    SELECT 000,3,3333,NULL,22222222,'ISET4711',80
    ), t2 AS (
    SELECT    ROW_NUMBER() OVER (PARTITION BY tblTest.OU,tblTest.SID,tblTest.CA,tblTest.CO ORDER BY sum(tblTest.Betrag) DESC) AS zeile,
            tblTest.OU, tblTest.SID, tblTest.CA, tblTest.CO, FORMAT(tblTest.SM,'00') + tblTest.UpID AS LatestFC, Sum(tblTest.Betrag) AS [Yearly Total]
    FROM    tblTest
    GROUP BY tblTest.OU, tblTest.SID, tblTest.CA, tblTest.CO, (FORMAT(tblTest.SM,'00') + tblTest.UpID)
    )
SELECT    t2.OU,t2.SID,t2.CA,t2.CO,t2.LatestFC,t2.[Yearly Total]
FROM    t2
WHERE    t2.zeile = 1
ORDER BY t2.OU,t2.SID,t2.CA,t2.CO
 
Richtig, aber durch den LEFT JOIN (der ja für die NULL-Werte wichtig ist) hol ich sie mir letztlich "zurück"... :confused:
Das ist nicht der Punkt, Dein falsches Ergebnis oben, enthält Werte aus T3, die eigentlich rausaggregiert sein müssen, laut Select in T3.
Deine Daten sagen aber was anderes.

Ich vermute, dass Du hier nicht mit Originalstatements /Daten arbeitetst und irgendwas bei Deiner Umsetzung fürs Forum schief geht.
Das übliche Vorgehen ist wie schon oft hier erläutert, dass man DDL und DML Statements liefert, damit extremes Kopfrechnen und all die ganzen Tippfehler schon mal als Fehlerquelle ausgeschlossen sind.
 
Das ist nicht der Punkt, Dein falsches Ergebnis oben, enthält Werte aus T3, die eigentlich rausaggregiert sein müssen, laut Select in T3.
Deine Daten sagen aber was anderes.

Ich vermute, dass Du hier nicht mit Originalstatements /Daten arbeitetst und irgendwas bei Deiner Umsetzung fürs Forum schief geht.
Das übliche Vorgehen ist wie schon oft hier erläutert, dass man DDL und DML Statements liefert, damit extremes Kopfrechnen und all die ganzen Tippfehler schon mal als Fehlerquelle ausgeschlossen sind.
Kapier ich leider net ;) oder wir reden aneinander vorbei? T3 muss ich anders aggregieren als T1...
Egal, es ist und bleibt schwer, seinen Fall so zu schildern, dass es jeder einfach versteht und ja, richtig, ich hatte abstrahiert für's Forum. Wobei der zu 100% keine Fehler enthält.
Habe nun eine Lösung über meinen VBA-Code indem ich temporär aus NULL " " mache, dann klappt es wunderbar... :)
Dennoch herzlichen Dank für Deine Denkanstöße, sie haben mich auf jeden Fall weitergebracht!
LG
 
Das ist nicht der Punkt, Dein falsches Ergebnis oben, enthält Werte aus T3, die eigentlich rausaggregiert sein müssen, laut Select in T3.
Deine Daten sagen aber was anderes.
Nein er joint die aggregierten Daten mit den selben Daten nochmal und hat dann irgendwo wieder alle Daten.
Wenn Du ein mini Skript mit Table Create und ein paar Inserts lieferst, ist es glasklar für jeden nachvollziehbar, ohne Kopfrechnen.
Du kannst meinen Test-Code nehmen als Tabelle:
Code:
WITH tblTest(OU,SM,SID,CA,CO,UpID,Betrag) AS (
    SELECT 000,1,3333,'000-1234',11111111,'ISET4711',10 UNION ALL
    SELECT 000,2,3333,'000-1234',22222222,'ISET4711',20 UNION ALL
    SELECT 000,3,3333,'000-1234',11111111,'ISET4711',30 UNION ALL
    SELECT 000,3,3333,'000-1234',22222222,'ISET4711',40 UNION ALL
    SELECT 000,3,3333,'000-1234',22222222,'ISET4711',50 UNION ALL
    SELECT 000,3,3333,'000-1234',NULL,'ISET4711',60 UNION ALL
    SELECT 000,3,3333,NULL,NULL,'ISET4711',70 UNION ALL
    SELECT 000,3,3333,NULL,22222222,'ISET4711',80
    )
Habe nun eine Lösung über meinen VBA-Code indem ich temporär aus NULL " " mache, dann klappt es wunderbar... :)
Hat meine Lösung #8 in Access nicht funktioniert? Geht da kein ROW_NUMBER()? Welche Access Version?
 
Nein er joint die aggregierten Daten mit den selben Daten nochmal und hat dann irgendwo wieder alle Daten.

Du kannst meinen Test-Code nehmen als Tabelle:
Code:
WITH tblTest(OU,SM,SID,CA,CO,UpID,Betrag) AS (
    SELECT 000,1,3333,'000-1234',11111111,'ISET4711',10 UNION ALL
    SELECT 000,2,3333,'000-1234',22222222,'ISET4711',20 UNION ALL
    SELECT 000,3,3333,'000-1234',11111111,'ISET4711',30 UNION ALL
    SELECT 000,3,3333,'000-1234',22222222,'ISET4711',40 UNION ALL
    SELECT 000,3,3333,'000-1234',22222222,'ISET4711',50 UNION ALL
    SELECT 000,3,3333,'000-1234',NULL,'ISET4711',60 UNION ALL
    SELECT 000,3,3333,NULL,NULL,'ISET4711',70 UNION ALL
    SELECT 000,3,3333,NULL,22222222,'ISET4711',80
    )

Hat meine Lösung #8 in Access nicht funktioniert? Geht da kein ROW_NUMBER()? Welche Access Version?
Danke für deine Mühe!
Ja, korrekt, bin kein Guru in diesem Gebiet, aber arbeite mit den mir bekannten Mitteln. Habe versucht meinen Fall so klar ich konnte zu formulieren, sorry, wenn es nicht gelungen ist oder es schwer ist, die Logik zu entschlüsseln.
Ne du, der Code funzt bei mir leider nicht... MS Access Pro Plus 2016
Mir ist auch noch nicht klar, wie dein Code die Maximalwerte für LatestFC hinbekommt...?
Wie gesagt, aktuell schieß ich die NULLwerte um, dann ein INNER JOIN und es funtzt ;)

Vielleicht auf der Wortebene noch ein Versuch:
Ich kann kein Aggregat mit MAX(LatestFC) vornehmen, weil sonst die SUM(Yearly Total) mit den dann entfallenen Datensätzen mit Aggregiert - es sei denn hier mache ich schon einen Fehler, weil ich eine Funktion nicht kenne?
Also war der Gedanke von zwei Abfragen und dann ein Zusammenführen...
 
Du kannst meinen Test-Code nehmen als Tabelle
Ja, danke, Dein Engagement in Ehren. Es geht mir aber nicht darum, dass Forenmitglieder Fleißarbeit der TE übernehmen.
Natürlich kann man die SQL im Kopf durchspielen, oder sich die Mühe machen, alles abzutippen, kopieren, usw. so wie Du und akretschmer. Aber das bietet immer gutes Fehlerpotential, besonders wenn es so aussieht, dass die Beispielstatements und Daten/Tabellen des TE bereits eine "Abstraktion" sind. Im Fall hier ergibt Statement und behauptetes Ergebnis m.E. keinen Sinn. Und allein darüber nachzudenken scheint mir müßig. SQL eignet sich prima zur vollständigen Prüfung. Man muss es nur einsetzen. Ich habe aber einfach selten Freude daran, sowas "abzutippen".

Wenn es am Ende lautet: zu kompliziert zu erklären, ist es eben nur die halbe Wahrheit, weil ein Script eindeutig wäre.

Und wenn dann noch daraus wird: ich hab da im Programmcode ein " " reingemacht, dann ist es natürlich ein mäßiges Ergebnis für so viel Anlauf.
 
Werbung:
Mir ist auch noch nicht klar, wie dein Code die Maximalwerte für LatestFC hinbekommt...?
Ich bekomme das Maximum über die Sortierung in dem ich dem der höchste Wert die zeilennr 1 bekommt und später auf die zeilennr filtere. Das ist sogar noch besser als deine Version mit max() ermitteln und dann joinen, die hat nämlich einen entscheidenden Haken. Wenn es zwei Zeilen mit der selben Summe gibt die dann beide gleich groß sind liefert dein Join doppelte Zeilen. Bei meiner Sortiervariante gäbe es dennoch nur eine Zeile.
 
Zurück
Oben