Task 9 - SQL

SELECT
    SUM(ASD.BETRAG) + SUM(ASD.BETRAGSTP) + SUM(ASD.CALCSTATSTEUERBETRAG) AS "Total Income",
    SWA.ISOWAEHRUNGSCODE AS "Currency order",
    SUM(ASDG.BETRAG) + SUM(ASDG.BETRAGSTP) + SUM(ASDG.CALCSTATSTEUERBETRAG) AS "Total Expenses",
    SW.ISOWAEHRUNGSCODE AS "Currency Tour",
    AKOPF.AUFTRAGSNUMMER AS "Order number",
    SH.BEZEICHNUNG AS "Order organisation",
    SK.NAME1 AS "Customer Name",
    SUM(ASD.BETRAG) AS "Amount order",
    SUM(ASD.BETRAGSTP) AS "Amount without TAX order",
    SUM(ASD.CALCSTATSTEUERBETRAG) AS "TAX Amount order",
--    SHI.BEZEICHNUNG AS "Tour Organisation",
--    ATOUR.TOURNUMMER AS "Tour numbers",
    LISTAGG(ATOUR.TOURNUMMER, ', ') WITHIN GROUP (ORDER BY ATOUR.TOURNUMMER) AS "Tour numbers",
    LISTAGG(SKUN.NAME1, ', ') WITHIN GROUP (ORDER BY SKUN.NAME1) AS "Carrier Name",
    LISTAGG(SHI.BEZEICHNUNG, ', ') WITHIN GROUP (ORDER BY SHI.BEZEICHNUNG) AS "Tour Organisation",
--    SKUN.NAME1 AS "Carrier Name",
    SUM(ASDG.BETRAG) AS "Amount tour",
    SUM(ASDG.BETRAGSTP) AS "Amount without TAX tour",
    SUM(ASDG.CALCSTATSTEUERBETRAG) AS "TAX Amount tour"

FROM
    AKOPF
LEFT JOIN
    SHIERARCHIE SH ON SH.ID = AKOPF.HIERARCHIE
LEFT JOIN
    ASENDUNG ON ASENDUNG.AKOPFNR = AKOPF.NR
LEFT JOIN
    SKUNDEN SK ON SK.NR = AKOPF.FRACHTAGNR
LEFT JOIN
    ASDGABRECHNUNG ASD ON ASD.ASENDUNGID = ASENDUNG.SENDUNGID
LEFT JOIN
    SWAEHRUNG SWA ON SWA.ID = ASD.WAEHRUNGID
LEFT JOIN
    ATASEND ON ASENDUNG.SENDUNGID = ATASEND.ASENDUNGID
LEFT JOIN
    ATOUR ON ATASEND.TOURNR = ATOUR.NR
LEFT JOIN
    SHIERARCHIE SHI ON SHI.ID = ATOUR.HIERARCHIE
LEFT JOIN
    SKUNDEN SKUN ON SKUN.NR = ATOUR.UNTERNEHMER
LEFT JOIN
    ASDGABRECHNUNG ASDG ON ASDG.ATAID = ATASEND.NR
LEFT JOIN
    SWAEHRUNG SW ON SW.ID = ASDG.WAEHRUNGID
WHERE
    AKOPF.AUFTRAGSNUMMER IN (633168, 633104, 634503)

GROUP BY
    AKOPF.AUFTRAGSNUMMER,
    SH.BEZEICHNUNG,
    SK.NAME1,
    SWA.ISOWAEHRUNGSCODE,
--    ATOUR.TOURNUMMER,
--    SHI.BEZEICHNUNG,
    SW.ISOWAEHRUNGSCODE;
--    SKUN.NAME1;