Totalsum query is working but...

[herbertioz]

I want to ask the database after the teams (lag) with the athletes listed in the team with the highest TotalVekt (TotalWeight).

With the code under I find the teams with the highest TotalWeight, but not with the athletes on the team and their weight combined with the totalweight of the team. Can someone help?
Lag = Teams
Deltagere = Athletes
Vekt = Weight

SELECT Lag.Lagnavn, Sum(Deltagere.Vekt) AS TotalVektLag
FROM Lag INNER JOIN Deltagere ON Lag.ID=Deltagere.LagID
GROUP BY Lag.Lagnavn;

 

[jal]

Try this:

SELECT Lag.*, Deltagere.*, TeamWeights.*
FROM ((Lag
INNER JOIN Deltagere ON Lag.ID=Deltagere.LagID)
INNER JOIN
(SELECT Lag.Lagnavn, Sum(Deltagere.Vekt) AS TotalVektLag
FROM Lag INNER JOIN Deltagere ON Lag.ID=Deltagere.LagID
GROUP BY Lag.Lagnavn) as TeamWeights
ON TeamWeights.Lagnavn = Lag.Lagnavn)

 

[herbertioz]

I have tried it and thats more like it. But I am not quite there yet. I modified the code a bit...

It lists the athletes in the team, with the teamname and the total teamweight. But how can I get just one instance with team with that team athletes, their weight and the totalweight?

Thanks for help, Jal!

SELECT Lag.Lagnavn, Deltagere.Navn, TeamWeights.TotalVektLag
FROM (Lag INNER JOIN (SELECT Lag.Lagnavn, Sum(Deltagere.Vekt) AS TotalVektLag
FROM Lag INNER JOIN Deltagere ON Lag.ID=Deltagere.LagID
GROUP BY Lag.Lagnavn)  AS TeamWeights ON Lag.Lagnavn = TeamWeights.Lagnavn) INNER JOIN Deltagere ON Lag.ID = Deltagere.LagID
ORDER BY TeamWeights.TotalVektLag DESC;

Try this:

SELECT Lag.*, Deltagere.*, TeamWeights.*
FROM ((Lag
INNER JOIN Deltagere ON Lag.ID=Deltagere.LagID)
INNER JOIN
(SELECT Lag.Lagnavn, Sum(Deltagere.Vekt) AS TotalVektLag
FROM Lag INNER JOIN Deltagere ON Lag.ID=Deltagere.LagID
GROUP BY Lag.Lagnavn) as TeamWeights
ON TeamWeights.Lagnavn = Lag.Lagnavn)

 

[herbertioz]

Sorry, the query is fine! I ordered the result in my report instead. Thank you very much Nice!

I have tried it and thats more like it. But I am not quite there yet. I modified the code a bit...

It lists the athletes in the team, with the teamname and the total teamweight. But how can I get just one instance with team with that team athletes, their weight and the totalweight?

Thanks for help, Jal!

SELECT Lag.Lagnavn, Deltagere.Navn, TeamWeights.TotalVektLag
FROM (Lag INNER JOIN (SELECT Lag.Lagnavn, Sum(Deltagere.Vekt) AS TotalVektLag
FROM Lag INNER JOIN Deltagere ON Lag.ID=Deltagere.LagID
GROUP BY Lag.Lagnavn)  AS TeamWeights ON Lag.Lagnavn = TeamWeights.Lagnavn) INNER JOIN Deltagere ON Lag.ID = Deltagere.LagID
ORDER BY TeamWeights.TotalVektLag DESC;