Max von Sum ermitteln

[Vertax]

Hallo Community,
ich hab mal eine frage:

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

     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:

     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.

 

[thomas_w]

AW: Max von Sum ermitteln

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

/* 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.

/* 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

 

[Vertax]

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:

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:

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)

 

[thomas_w]

AW: Max von Sum ermitteln

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

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

 

[Vertax]

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 !