How to fetch the data from multiple tables order by SUM query?-CodePudding

I have different tables like profiles, tournaments, tournamentdate, tournamentresult and pigeons.

Profiles columns > ProfileId, Name, Address, etc.
Tournaments columns > tID, name, detail, startdate, enddate, etc.
Tournamentdate columns > tdID, tID, date.
Tournamentresult Columns > ResultID, ProfileID, tID, tdID, starttime and Total (Total Time).
Pigeons Columns > PID, ResultID, ProfileID, TID, TDID, Pigeon Number, FlyingTime, TotalTime.

I have create a tournament for 3 days like 13,15,17 May (added dates into tournamentdate Table). I have added 5 peoples from Profiles Table, 7 Pigeons a day into Pigeons Table.

I will show you how to I display data using PHP.

this is one day record one Person have 7 Pigeons I am using SQL Sum Query for each person for total. Now I want to display same thing but Total as DESC.

Please tell me how to I Display as total DESC.
If you give me code I will be grateful to you۔

CodePudding user response：

You can do this by creating a SELECT statement to get all the users and inside the SELECT statement you can nest another SELECT to get the total score. Then you can simply sort your result using ORDER.

SELECT
    *,
    (SELECT SUM(score) FROM games WHERE user = users.ID) AS total
FROM users ORDER BY total DESC

In your example, if I understand correctly, this would translate to:

SELECT
    *, (SELECT SUM(TotalTime) FROM Pigeons WHERE ProfileID = Profiles.ProfileId) AS total
FROM Profiles ORDER BY total DESC

CodePudding user response：

Use group_concat along with group by you can get all data in a single query. then you can use those data using explode.