SUM and GROUP BY two times

[r243]

Hi guys,
Sorry if the title is a little cryptic, but I really don't know how to describe this problem.

I am currently developing a browser based game, where each player has one or more bases. Each base has an amount of XP, so the user's total XP is the sum of all his bases XP.
I use this total amount to rank the players, and the query I use is the following:

SELECT tbluser.uID, tbluser.uName, SUM(tblbase.basXP) AS uXP
FROM tbluser INNER JOIN tblbase ON tbluser.uID = tblbase.basUserID
GROUP BY tblbase.basUserID
ORDER BY uXP DESC


This works perfectly fine. Output of this is:
uID | uName | uXP
------------------
1 player1 2500
5 player2 2100
3 player3 940
etc.

To the problem:
Some of the users are grouped into alliances. What I want now is to rank these alliances by calculating the total XP in each alliance, which is the sum of all the alliance members' total XP.

I understand that I in some way have to use a similar query like the one above, but I can't make it work.

I would like my output like this:
allID | allName | allTotXP
-----------------------------
3 alliance1 12400
5 alliance2 9600
1 alliance3 5400
etc.

The 'tbluser' table contains a column uAllID which equals the unique index allID in the 'tblalliance' table.


If anyone has an idea of how to solve this one it will be very much appreciated.
Thanks,
henrik

 

[Kozusnik]

It looks like you should just need another left join and change the group to allID.

SELECT tbluser.uAllID, tblalliance.allNmae, SUM(tblbase.basXP) AS uXP FROM tbluser 
INNER JOIN tblbase ON tbluser.uID = tblbase.basUserID
LEFT OUTER JOIN tblalliance ON tblalliance.allID=tbluser.uAllID
        GROUP BY tbluser.allName
            ORDER BY uXP DESC

If this isn't it, please post the table descriptions with some sample data and we'll help further.
Mark

 

[r243]

Perfect! Exactly what I was looking for, thanks alot!
Had to tweak the code a little tho, or I'd also get the total XP for users who didn't have an alliance, but now it works as it should.

The final query is:

SELECT tbluser.uAllID, tblalliance.allName, SUM(tblbase.basXP) AS allXP FROM tbluser 
    INNER JOIN tblbase ON tbluser.uID = tblbase.basUserID
        LEFT OUTER JOIN tblalliance ON tblalliance.allID = tbluser.uAllID
            WHERE tbluser.uAllID > 0
                GROUP BY tblalliance.allID
                    ORDER BY allXP DESC

Thanks again and regards,
henrik