1. Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm
    Information ausblenden

Summe eines Subqueries

Dieses Thema im Forum "MySQL und MariaDB" wurde erstellt von basementmedia, 9 Februar 2018.

  1. basementmedia

    basementmedia Benutzer

    Hi zusammen,

    ich möchte aus den einzelnen Zeilen eines Querys eine Summe bilden:

    SELECT
    ROUND(((sum(stunden.stunden))*(ROUND(((ROUND((projekte.hmax * projekte.hsatz),2))/((SELECT sum(stunden) FROM stunden WHERE projekt_id=projekte.ID))),2) )),2) as peuro_h_u
    FROM stunden
    LEFT JOIN projekte ON stunden.projekt_id = projekte.ID
    WHERE stunden.user_id = 27 AND (stunden.datum between '2018-01-01' AND '2018-01-31')
    GROUP BY stunden.projekt_id
    ORDER BY sum(stunden.stunden) ASC

    Folgendes klappt zwar:
    SELECT SUM(peuro_h_u) as peuro_h_u_summe FROM (SELECT
    ROUND(((sum(stunden.stunden))*(ROUND(((ROUND((projekte.hmax * projekte.hsatz),2))/((SELECT sum(stunden) FROM stunden WHERE projekt_id=projekte.ID))),2) )),2) as peuro_h_u
    FROM stunden
    LEFT JOIN projekte ON stunden.projekt_id = projekte.ID
    WHERE stunden.user_id = 27 AND (stunden.datum between '2018-01-01' AND '2018-01-31')
    GROUP BY stunden.projekt_id
    ORDER BY sum(stunden.stunden) ASC) as peuro_h_u_summe

    Das Problem ist nur, dass ich diesen Query bereits in einen anderen Subquery verschachteln will, um im Anschluss daran die user.ID (im Beispiel oben fix auf 27 gesetzt) vom Parent-Query zu beziehen, wie nachfolgend dargestellt:

    SELECT
    u.ID as dataset_id,
    u.name,
    (
    SELECT SUM(peuro_h_u) as peuro_h_u_summe FROM (SELECT
    ROUND(((sum(stunden.stunden))*(ROUND(((ROUND((projekte.hmax * projekte.hsatz),2))/((SELECT sum(stunden) FROM stunden WHERE projekt_id=projekte.ID))),2) )),2) as peuro_h_u
    FROM stunden
    LEFT JOIN projekte ON stunden.projekt_id = projekte.ID
    WHERE stunden.user_id = u.ID AND (stunden.datum between '2018-01-01' AND '2018-01-31')
    GROUP BY stunden.projekt_id
    ORDER BY sum(stunden.stunden) ASC) p) as peuro_summe
    FROM user u ORDER BY u.name

    Da ich hier dann eine doppelte Verschachtelung habe, kennt er u.ID im Unter-Unter-Query nicht.
    Und ich komm einfach nicht drauf, wie ich die doppelte Verschachtelung umgehen kann.

    Kann mir irgendjemand einen Tipp geben.

    Grüßle
    Daniel
     
  2. akretschmer

    akretschmer Datenbank-Guru

    kannst Du das an einem minimalisierten Beispiel mit Tabellendefinition,Beispieldaten und Wunschresultat nachvollziehbar darstellen?
     
  3. basementmedia

    basementmedia Benutzer

    Hi,

    ich versuchs mal:

    Ich hab eine Tabelle "Stunden" in der erfasst ist, welcher User wann wieviele Stunden auf ein Projekt gebucht hat.
    Die Tabelle ist (vereinfacht) so aufgebaut:

    ID datum user_id projekt_id stunden
    ___________________________________________
    1 2018-01-01 27 1 6
    2 2018-01-05 13 2 3
    usw.

    Dann hab ich noch ne andere Tabelle Projekte, in der alle Projekte erfasst werden. Zu jedem Projekt ist erfasst, wieviele Stunden man maximal benötigen darf (hmax) und welchen Stundensatz das Projekt hat (hsatz).

    Nun will ich eine Auswertung fahren, die berechnet, wieviel Euro jeder User innerhalb eines bestimmten Zeitraums erwirtschaftet hat. Dazu sollen die folgenden Berechnungen (möglichst in einem einzelnen Query) gefahren werden:

    Mache für jeden User folgendes:
    1. Ermittle die Stundensumme pro Projekt innerhalb des betrachteten Zeitraums (h_user)
    2. Ermittle die GESAMTE Stundensumme (=hist), die auf das Projekt bereits gebucht wurde (also auch von anderen Usern).
    3. Ermittle den Ertrag des Projekts pro Stunden (h_pro_stunde=(hmax)*(hsatz)/hist).
    3. Ermittle den Anteil des Users am Ertrag im betrachteten Zeitraum (=h_pro_stunde * h_user.

    Soviel zur Erklärung des vorhabens.
    Innerhalb der Auswertung werden aber noch ganz viele andere Berechnungen gefahren, d.h. die oben beschriebene Teilauswertung bezieht ihre user_id jeweils von einem übergeordneten Query, der in einer Tabelle für jeden User einen Zeile erzeugt und dazu dann in verschiedenen Spalten verschiedene Auswertungen ausgibt.

    Vereinfacht sieht mein Problem von der rein technischen Seite also so aus:

    ich habe einen Hauptquery (den hab ich nun mal vereinfach):

    SELECT
    u.ID as dataset_id,
    u.name
    FROM user u ORDER BY u.name


    D.h. es wird erstmal nur der Name aller User ausgegeben.

    Außerdem hab ich einen weiteren Query, der mir zu jedem User eine Teilauswertung ausgibt (hier stark vereifacht, nur die Summe der gebuchten Projektstunden des Users pro Projekt werden ausgegeben. Außerdem ist die User_ID fix auf 27 gesetzt):

    SELECT sum(stunden.stunden)
    FROM stunden
    LEFT JOIN projekte ON stunden.projekt_id = projekte.ID
    WHERE stunden.user_id = 27 AND (stunden.datum between '2018-01-01' AND '2018-01-31')
    GROUP BY stunden.projekt_id


    Im ersten Schritt möchte ich nun die Summe aus den einzelnen Zeilen, die der Query erzeugt, d.h. quasi

    SELECT SUM(stunden) as summe_stunden FROM (
    SELECT sum(stunden.stunden) as stunden
    FROM stunden
    LEFT JOIN projekte ON stunden.projekt_id = projekte.ID
    WHERE stunden.user_id = 27 AND (stunden.datum between '2018-01-01' AND '2018-01-31')
    GROUP BY stunden.projekt_id

    )

    Und im letzten Schritt will ich diese Summe nun nicht nur vom User mit der ID 27 sondern von jedem User im Parent-Query.
    Also quasi:

    SELECT
    u.ID as dataset_id,
    u.name,

    SELECT SUM(stunden) FROM (
    SELECT sum(stunden.stunden)
    FROM stunden
    LEFT JOIN projekte ON stunden.projekt_id = projekte.ID
    WHERE stunden.user_id = u.ID AND (stunden.datum between '2018-01-01' AND '2018-01-31')
    GROUP BY stunden.projekt_id

    ) es summe_stunden

    FROM user u ORDER BY u.name

    Und das funktioniert eben nicht mehr, weil u.ID zu tief verschachtelt ist.

    Mir fällt aber kein Weg ein, diese Umschachtelung zu umgehen.

    Puh, sorry, wegen dem vielen Text aber ich hoffe es ist nun etwas verständlicher.

    Viele Grüße
    Daniel
     
  4. basementmedia

    basementmedia Benutzer

    statt "es summe_stunden" meinte ich natürlich "as summe_stunden".
     
  5. akretschmer

    akretschmer Datenbank-Guru

    Ich werde nicht ganz schlau draus, vermute aber, daß Du gruppenweise summieren willst, also die Stunden per User und per Projekt und so, oder?

    Code:
    test=*# select * from stunden ;
     id |  datum  | user_id | projekt_id | stunden
    ----+------------+---------+------------+---------
      1 | 2018-01-01 |  27 |  1 |  6
      2 | 2018-01-05 |  13 |  2 |  3
      3 | 2018-01-07 |  27 |  2 |  12
    (3 Zeilen)
    
    test=*# select * from projekte;
     id |  name  | hmax | hsatz
    ----+-----------+------+-------
      1 | projekt 1 |  10 |  5
      2 | projekt 2 |  20 |  10
    (2 Zeilen)
    
    test=*# select stunden.user_id, stunden.projekt_id, sum(stunden.stunden) over (partition by user_id) as stunden_per_user, sum(stunden) over (partition by projekt_id) as stunden_per_projekt from stunden left join projekte on stunden.projekt_id=projekte.id where stunden.user_id in(13, 27) and stunden.datum between '2018-01-01' and '2018-01-31' order by user_id, projekt_id;
     user_id | projekt_id | stunden_per_user | stunden_per_projekt
    ---------+------------+------------------+---------------------
      13 |  2 |  3 |  15
      27 |  1 |  18 |  6
      27 |  2 |  18 |  15
    (3 Zeilen)
    
    
    kommt das hin?
     
  6. basementmedia

    basementmedia Benutzer

    Puh: sum() over () und (partition by) kenn ich bisher noch garnicht.
    Da muss ich ich mal kurz mit befassen um beantworten zu können, ob das mein Problem löst.
    Denn im oberen Beispiel hab ich nun ja alles stark vereinfacht.
     
  7. basementmedia

    basementmedia Benutzer

    By the way:
    Dein Query erzeugt bei mir erstmal einen Fehler.
    Kann es sein, dass partition by mit mysql nicht funktioniert?
     
  8. akretschmer

    akretschmer Datenbank-Guru

    Ja, die schlechte Nachricht für Dich: MySQL kann das (und ganz viele andere Dinge) NICHT.
     
  9. basementmedia

    basementmedia Benutzer

    Gibt es denn eine andere Möglichkeit, um von einem doppelt verschachtelten Query auf einen Alias eines äußersten Queries zu zugreifen? Oder wie ich meinen Query vereinfach könnte?
     
  10. akretschmer

    akretschmer Datenbank-Guru

    Es gibt einen LATERAL JOIN, mit dem man quasi auf die 'äußere' Abfrage zugreifen kann. Mal als Demo.

    Du hast eine Tabelle wie diese:

    Code:
    test=*# select * from lateral_demo ;
     id | c
    ----+---
      1 | 5
      2 | 7
      3 | 2
    (3 Zeilen)
    
    Du willst jetzt jede dieser Einträge um die Anzahl in der Spalte c vervielfältigen. Also id=1 5 mal, ID=2 7 mal und so weiter.

    Die einfach Methode geht nicht:

    Code:
    test=*# select * from lateral_demo l left join (select * from generate_series(1, l.c)) foo on true;
    ERROR:  invalid reference to FROM-clause entry for table "l"
    ZEILE 1: ...emo l left join (select * from generate_series(1, l.c)) foo ...
      ^
    TIP:  There is an entry for table "l", but it cannot be referenced from this part of the query.
    
    Aber mit dm LATERAL JOIN geht es:

    Code:
    test=*# select * from lateral_demo l left join lateral (select * from generate_series(1, l.c)) foo on true;
     id | c | generate_series
    ----+---+-----------------
      1 | 5 |  1
      1 | 5 |  2
      1 | 5 |  3
      1 | 5 |  4
      1 | 5 |  5
      2 | 7 |  1
      2 | 7 |  2
      2 | 7 |  3
      2 | 7 |  4
      2 | 7 |  5
      2 | 7 |  6
      2 | 7 |  7
      3 | 2 |  1
      3 | 2 |  2
    (14 Zeilen)
    
    test=*#
    

    Ach, und bevor ich es vergesse: MySQL kann diesen JOIN nicht. Wie so vieles nicht. Ja, ich wiederhole mich.
     
  11. basementmedia

    basementmedia Benutzer

    Haha....danke ;-)
    Ich hab aber eben in meinem Fall leider keine Wahlmöglichkeit.

    Ich versuchs mal weiter:
    Gibt es ne Möglichkeit, die Summe aus den Einzelergebnissen zu bilden, die dieser Query liefert, OHNE dazu eine "Ummantelung" mit einem weiteren SELECT Statement zu machen?

    SELECT sum(stunden.stunden)
    FROM stunden
    LEFT JOIN projekte ON stunden.projekt_id = projekte.ID
    WHERE stunden.user_id = 27 AND (stunden.datum between '2018-01-01' AND '2018-01-31')
    GROUP BY stunden.projekt_id

    Also quasi sowas "ähnliches" wie:

    SELECT sum(sum(stunden.stunden)) ....

    Was natürlich an dieser Stelle völliger Quatsch ist und ur zur Demonstration dienen soll....
     
  12. basementmedia

    basementmedia Benutzer

    Gibts nicht ne Möglichkeit, mit Variablen zu arbeiten, also sum=sum+(sum.stunden) oder was ähnliches.
     
  13. akretschmer

    akretschmer Datenbank-Guru

    probier es ;-)
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden