Ergebnis auf einen Datensatz reduzieren

[HCB]

Nun ich schon wieder
Bitte um Hilfe von den SQL Profis hier. Ich habe eine SQL Abfrage die mir die Gesamtzahl der Lieferungen eines Artikels zeigen soll:

Delphi-Quellcode:

 SELECT
    ABFDocAuftragNr,
    ABFPosMenge,
    ABFPosArtNr,
    (
        SELECT distinct SUM(ABFPosMenge)
        FROM ABFDok AS D
        INNER JOIN ABFPos AS P ON D.ABFDocID = P.ABFPosdocID
        WHERE D.ABFDocOptFertig = false AND
              D.ABFDocVisType = 2 AND
              P.ABFPosEPreis > 0 AND
              P.ABFPosType IN (0, 7) AND
              P.ABFPosNr <> '' AND
              P.ABFPosArtNr = ABFPos.ABFPosArtNr
    ) AS TotalSum
FROM
    ABFDok
INNER JOIN
    ABFPos ON ABFDok.ABFDocID = ABFPos.ABFPosdocID
WHERE
    ABFDocOptFertig = false AND
    ABFDocVisType = 2 AND
    ABFPosEPreis > 0 AND
    ABFPosType IN (0, 7) AND
    ABFPosNr <> ''
ORDER BY ABFPosArtNr;

Das Ergebis sieht so aus:

ABFDocAuftragNr ABFPosMenge ABFPosArtNr TotalSum
Text[8] Currency Text[22] Currency
------------------------------------------------------------------
A023705 10 1031009-011 10
A023705 10 1031010-046 10
A023705 12 1031019-017 12
A023705 5 1031040-006 5
A023705 3 1036020-002 12
A023705 9 1036020-002 12
A023705 2 1036023-005 2
A023705 5 1041812 5
A023854 5 1061853 5
A023850 1 1062558 1
A023850 1 1062559 1
A023763 1 1119022 3
A023763 2 1119022 3

Nun wird der Artikel 1036020-002 zweimal aufgeführt, die TotalSum passt also 1 x 3 St. + 1 x 9 St. = 12.

Frage aller Fragen: Wie kann ich es bewerkstelligen, dass nur einmal der Datensatz angezeigt wird, also jede Artikelnummer einzeln und nicht doppelt?
Wenn ihr helfen könntet, wäre mein Tag super.

LG Harry

[stifflersmom]

So wie ich das (ungetestet) sehe, könntest Du eigentlich auf den subselect verzichten und gleich ein SUM auf ABFPosMenge machen, musst halt nur ein group by hinzufügen.

[Uwe Raabe]

Um das mal zu konkretisieren (nur so hingeschrieben):

SQL-Code:

SELECT
    ABFDocAuftragNr,
    ABFPosArtNr,
    SUM(ABFPosMenge) AS TotalSum
FROM
    ABFDok
INNER JOIN
    ABFPos ON ABFDok.ABFDocID = ABFPos.ABFPosdocID
WHERE
    ABFDocOptFertig = false AND
    ABFDocVisType = 2 AND
    ABFPosEPreis > 0 AND
    ABFPosType IN (0, 7) AND
    ABFPosNr <> ''
GROUP BY ABFPosArtNr, ABFDocAuftragNr
ORDER BY ABFPosArtNr;

[HCB]

Hallo Danke, Super!
Jetzt wird es aber wesentlich komplexer mit zwei Tabellen:

1. ABFDok (da sind nur die Dokumente wie Auftrag, Lieferschein, Rechnung etc. gespeichert)

2. ABFPos (da sind die einzelenen Positionen des Auftrags, Lieferschein etc.)
Nun habe ich z.B. im Auftrag eine Bestellmenge eines Artikels von 12 St.

Anhand der ABFDocAuftragsID 106921 = ABFDocID brauche ich in der ABFPos unter der ABFPosDocId die bestellte Menge unter ABFPosMenge = 10St.
Jetzt suche ich alle gelieferten Artikel anhand des Lieferscheins ABFDocVisType = 2 mit der ABFDocID 107348 in ABFPos die gelieferte Menge = 1
Nun wurde aber nochmals eine Teilmenge geliefert, mit Lieferschein ABFDocVisType = 2 und der ABFDocID 1073347 in ABFPos die gelieferte Menge = 2

Da ich jetzt alles habe, soll mir die noch zu liefernde Menge angezeigt werden: 10 St aus Auftrag minus 1 St aus Lieferung1 minus 2 St. aus Lieferung2 also noch 7 st. zu liefern.

Mir brummt schon der Kopf! Beide Queries zu verbinden scheitert mangels Fachkenntissen.

Delphi-Quellcode:

SELECT
    ABFDocAuftragNr,
    ABFDocDatum,
    ABFDocKundeKurzbez,
    ABFDocSummeNetto_0,
    ABFPosMenge as bestellt,
    ABFPosArtNr,
    ABFPosName1
FROM
    ABFDok
INNER JOIN
    ABFPos ON ABFDok.ABFDocAuftragID = ABFPos.ABFPosDocID
WHERE
    ABFDocOptFertig = false AND
    ABFDocVisType = 1 AND
    ABFPosEPreis > 0 AND
    ABFPosType IN (0, 7) AND
    ABFPosNr <> ''
order by ABFDocAuftragNr;

und wie von Uwe gepostet:

Delphi-Quellcode:

SELECT
    ABFDocAuftragNr,
    ABFPosArtNr,
    SUM(ABFPosMenge) AS TotalSum
FROM
    ABFDok
INNER JOIN
    ABFPos ON ABFDok.ABFDocID = ABFPos.ABFPosdocID
WHERE
    ABFDocOptFertig = false AND
    ABFDocVisType = 2 AND
    ABFPosEPreis > 0 AND
    ABFPosType IN (0, 7) AND
    ABFPosNr <> ''
GROUP BY ABFPosArtNr, ABFDocAuftragNr
ORDER BY ABFPosArtNr;

Anbei Screenshots.
Ich hoffe auf euere Hilfe.

LG Harry

[HCB]

Keine Ideen?
Hab es jetzt mal mit temp tabelle versucht, es werden die Mengen eines Artikel summiert. Leider funktioniert die anschließende Update-Anweisung nicht.
Sieht jemand von der Cracks woran es liegt?

Delphi-Quellcode:

 UPDATE Tmp_Results
SET TotalMenge = SubQuery.TotalMenge
FROM Tmp_Results
INNER JOIN (
    SELECT ABFPosArtNr, SUM(ABFPosMenge) AS TotalMenge
    FROM ABFDok
    INNER JOIN ABFPos ON ABFDok.ABFDocID = ABFPos.ABFPosdocID
    WHERE ABFDocOptFertig = 0 AND
          ABFDocVisType = 2 AND
          ABFPosEPreis > 0 AND
          ABFPosType IN (0, 7) AND
          ABFPosNr <> ''
    GROUP BY ABFPosArtNr
) AS SubQuery ON Tmp_Results.ABFPosArtNr = SubQuery.ABFPosArtNr;

LG Harry

[Jasocul]

Wir haben in vielen Bereichen heute einen Feiertag. Da ist im Forum meist nicht viel los. Morgen ist dann dementsprechend Brückentag
Nur mal auf die Schnelle:

Code:

select
    ABFDocAuftragNr,
    ABFDocDatum,
    ABFDocKundeKurzbez,
    ABFDocSummeNetto_0,
    ABFPosMenge as bestellt,
    ABFPosArtNr,
    ABFPosName1,
    (select sum(LIPos.ABFPosMenge) as LIMenge from ABFPos as LIPos where LIPos.ABFPosdocID = LI.ABFDocID) as geliefert
from ABFDok as AU
join ABFPos as AUPos
ON AU.ABFDocAuftragID = AUPos.ABFPosDocID
left outer join ABFDok as LI
on AU.ABFDocAuftragNr = LI.ABFDocAuftragNr
where AU.ABFDocOptFertig = false AND
    AU.ABFDocVisType = 1 AND
    AUPos.ABFPosEPreis > 0 AND
    AUPos.ABFPosType IN (0, 7) AND
    AUPos.ABFPosNr <> ''

Das ist völlig ungeprüft und ohne genaue hinzusehen.
Ob das in Access so funktioniert, weiß ich nicht und dein Update musst du dann gegebenenfalls selbst daraus erstellen.