1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies. Weitere Informationen
  2. Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, Oracle, Sql-Server, Postgres, Access uvm
    Information ausblenden

Max von Sum ermitteln

Dieses Thema im Forum "MySQL" wurde erstellt von Vertax, 31 Oktober 2010.

  1. Vertax

    Vertax Benutzer

    Hallo Community,
    ich hab mal eine frage:

    Über diesen SQL Query lasse ich mir die summe aller kosten pro mitarbeiter berechnen bei einer reise:

    Code:
         SELECT SUM(Reise.Kosten) Sum
     FROM Mitarbeiter, Reise,Abteilung
     WHERE (Mitarbeiter.PNr = Reise.Mitarbeiter and Mitarbeiter.ANr = Abteilung.ANr)
     Group by Mitarbeiter.Name Order by sum desc 
    So das klappt auch soweit, nur möchte ich nun das max bei dieser summe auslesen und den dazugehörigen namen, abteilungsname, personalnummer ausgeben.


    Ich hatte gedacht das ich das vielleicht mit subquerys machen muss in der art:

    Code:
     
         SELECT M.Name,M.PNr,A.AName,max(Summe.sum) f
    FROM 
    (
     SELECT SUM(R.Kosten) sum,M.Pnr,R.Kosten
     FROM Mitarbeiter M, Reise R,abteilung A
     WHERE (M.PNr = R.Mitarbeiter and M.ANr = A.ANr)
     Group by M.Name Order by sum desc
     )
     Summe,Mitarbeiter M ,Abteilung A,Reise R
     where (M.ANr = A.ANr) and (M.Pnr= summe.pnr) 
     Group by M.name order by f desc 
     
    doch ich erhalte nicht nur die tuppels mit den höchsten reise kosten sondenr alle. wo könnte denn der hund begraben liegen?

    btw desc 1 zu stellen ist keine alternative, da mehrere personen den selben wert annehmen können und deshalb eine mehrfache ausgabe geschehen muss.
     
  2. thomas_w

    thomas_w Datenbank-Guru

    AW: Max von Sum ermitteln

    Anstatt dieser Auswertung (es könnte ja mehrere "Maier" geben )

    Code:
    /* Summe Reisenkosten pro Name */
    SELECT SUM(Reise.Kosten) AS sum_rk
      FROM Mitarbeiter, Reise,Abteilung
     WHERE Mitarbeiter.PNr = Reise.Mitarbeiter 
       and Mitarbeiter.ANr = Abteilung.ANr
     GROUP BY Mitarbeiter.Name
     ORDER BY sum_rk DESC
    
    solltest Du für Deine SUB-Query diese Abfragen verwenden. Dann klappt es mit dem JOIN besser, da Dir die PKs für die Verknüpfung korrekt zur verfügung stehen.

    Code:
    /* Summe Reisenkosten pro PNr */
    SELECT Mitarbeiter.PNr, SUM(Reise.Kosten) AS sum_rk
      FROM Mitarbeiter, Reise,Abteilung
     WHERE Mitarbeiter.PNr = Reise.Mitarbeiter 
       and Mitarbeiter.ANr = Abteilung.ANr
     GROUP BY Mitarbeiter.PNr
     ORDER BY sum_rk DESC 
     
     
    /* Summe Reisenkosten pro Abteilung */
    SELECT Abteilung.ANr, SUM(Reise.Kosten) AS sum_rk
      FROM Mitarbeiter, Reise,Abteilung
     WHERE Mitarbeiter.PNr = Reise.Mitarbeiter 
       and Mitarbeiter.ANr = Abteilung.ANr
     GROUP BY Abteilung.ANr
     ORDER BY sum_rk DESC
    
    Grüße
    Thomas
     
  3. Vertax

    Vertax Benutzer

    AW: Max von Sum ermitteln

    Hi Thomas, danke für dein Hinweis. In der Tat ist es sinnvoller über die PNr. zu Gruppieren. Ich hänge aber immer noch an einem kleinen Problem:

    Code:
    SELECT  MAX(sum_rk) as Maximum,M.Name ,M.PNr,A.AName
    FROM(
     SELECT  Mitarbeiter.PNr, SUM(Reise.Kosten) AS sum_rk
      FROM Mitarbeiter,  Reise,Abteilung
     WHERE Mitarbeiter.PNr = Reise.Mitarbeiter 
       and  Mitarbeiter.ANr = Abteilung.ANr
     GROUP BY Mitarbeiter.PNr
     ORDER  BY sum_rk DESC 
    ) as Summe,Mitarbeiter M,Abteilung A
    where (M.ANr =  A.ANr) and (m.PNR=summe.pnr) and[B] (sum_rk > 3000[/B])
    Group By  M.PNr 
    So habe mal den Teil der mich belastet hervorgehoben.
    Und zwar ist das Problem folgendes, wenn ich die summe mittels einen operators vergleiche funktioniert die sql abfrage einwandfrei.

    Wenn ich allerdings schreibe: sum_rk = Maximum bekomme ich den Fehler -> Error Code: 1054 Unknown column 'Maximum' in 'where clause'

    Also wurde der Alias ja wohl nicht vergeben/kann nicht vergeben werden.
    Ok habe dann probiert sum_rk = MAX(sum_rk) was aber auch nur in eine Error Meldung endet ->
    Error Code: 1111 Invalid use of group function.

    ...

    so 3 Stunden später:

    Code:
    SELECT  MAX(sum_rk) as Maximum,M.Name ,M.PNr,A.AName
    FROM(
     SELECT  Mitarbeiter.PNr, SUM(Reise.Kosten) AS sum_rk
      FROM Mitarbeiter,  Reise,Abteilung
     WHERE Mitarbeiter.PNr = Reise.Mitarbeiter 
       and  Mitarbeiter.ANr = Abteilung.ANr
     GROUP BY Mitarbeiter.PNr
     ORDER  BY sum_rk DESC 
    ) as s,Mitarbeiter M,Abteilung A
    where (M.ANr =  A.ANr)
    and (m.PNR=s.pnr) 
    and s.sum_rk =
    (
    select  max(sum_rk)
    )
    group by M.PNr;
    Sehe ich das richtig das ich die Selektierung der tupel mit den Maximalewerten in der where klausel vornehmen muss?

    Bei meiner letzten Version bekomme ich zwar Reise-Gesamtkosten, PNr, Name und Abteilung angezeigt, aber halt leider auch für alle sum_rk != max(sum_rk)
     
  4. thomas_w

    thomas_w Datenbank-Guru

    AW: Max von Sum ermitteln

    Probier doch mal HAVING(), damit kannst Du auf ein Ergebnis aus dem GROUP BY zugreifen.

    Code:
    SELECT Max(sum_rk) AS maximum,
           MAX(m.name) AS name,
           m.pnr,
           MAX(a.aname) AS aname
      FROM (SELECT mitarbeiter.pnr,
                   SUM(reise.kosten) AS sum_rk
            FROM   mitarbeiter,
                   reise,
                   abteilung
            WHERE  mitarbeiter.pnr = reise.mitarbeiter
                   AND mitarbeiter.anr = abteilung.anr
            GROUP  BY mitarbeiter.pnr
            ORDER  BY sum_rk DESC
           ) AS summe,
           mitarbeiter m,
           abteilung a
     WHERE  m.anr = a.anr 
       AND  m.pnr = summe.pnr 
    GROUP  BY m.pnr  
    HAVING MAX(sum_rk) > 3000;
    
    Die MAX() in der oberen SELECT-Liste habe ich eingefügt, damit die Abfrage dem SQL-Standard
    enspricht. MySQL erlaubt leider solche "falschen" GROUP BY Konstrukte, andere Datenbanken nicht.

    Grüße
    Thomas
     
  5. Vertax

    Vertax Benutzer

    AW: Max von Sum ermitteln

    Vielen lieben dank,das having war der entscheidende tipp :)
    Bin erst seit ner Woche im SQL Geschäft und bei dem Kapitel mit Having waren wir in der Vorlesung noch nicht.

    ..... In der Übung wird es aber benötigft oO Paradox !
     
Die Seite wird geladen...

Diese Seite empfehlen