For some reason I can't get my head around this... And I know it should be easy
(relatively) I have made a quick/silly poker website for my weekly game - and I
am charting the results on the site... But I want to be able to combine results
- so I can show the grand total of winnings (1st + 2nd + 3rd = MyTotalWinnings)
sort of thing... Right now I can get 1st, 2nd and 3rd - as they are separate DB
fields... But I am having a devil of a time SUMing the info.
What I have - & Current Querries I need to combine:
<!--- Get Game Stats range--->
<cfquery NAME="qryGetGameStats" datasource="#datasource#">
SELECT
COUNT(*) AS TotalGames,
MIN(ThirdWin) AS SmallWin,
MAX(FirstWin) AS BigWin,
SUM(FirstWin) As FrstWin,
SUM(SecondWin) As SecWin,
SUM(ThirdWin) As ThrdWin,
SUM(Buyin) As BuyTot
FROM Game
</cfquery>
<!--- Get Game Winner 1st place --->
<cfquery name="qryGetPokerWinner1" datasource="#datasource#">
SELECT
Game.FirstPly, Members.NickName,
SUM(FirstWin) AS Winnings
FROM Game, Members
WHERE Game.FirstPly = Members.MemberID
GROUP BY Game.FirstPly, Members.NickName
ORDER BY NickName ASC
</cfquery>
<!--- Get Game Winner 2nd place --->
<cfquery name="qryGetPokerWinner2" datasource="#datasource#">
SELECT
Game.SecondPly, Members.NickName,
SUM(SecondWin) AS Winnings
FROM Game, Members
WHERE Game.SecondPly = Members.MemberID
GROUP BY Game.SecondPly, Members.NickName
ORDER BY NickName ASC
</cfquery>
<!--- Get Game Winner Third place --->
<cfquery name="qryGetPokerWinner3" datasource="#datasource#">
SELECT
Game.ThirdPly, Members.NickName,
SUM(ThirdWin) AS Winnings
FROM Game, Members
WHERE Game.ThirdPly = Members.MemberID
GROUP BY Game.ThirdPly, Members.NickName
ORDER BY NickName ASC
</cfquery>
<!--- Get The Total Buyin --->
<cfquery name="qryGetBuyin" datasource="#datasource#">
SELECT Game.GameName, Game.gameID, Members.NickName,
SUM(Game.Buyin) AS Losses
FROM Game, Members
WHERE Members.MemberID = Game.P1
OR Members.MemberID = Game.P2
OR Members.MemberID = Game.P3
OR Members.MemberID = Game.P4
OR Members.MemberID = Game.P5
OR Members.MemberID = Game.P6
OR Members.MemberID = Game.P7
OR Members.MemberID = Game.P8
OR Members.MemberID = Game.P9
OR Members.MemberID = Game.P10
OR Members.MemberID = Game.P11
OR Members.MemberID = Game.P12
OR Members.MemberID = Game.P13
OR Members.MemberID = Game.P14
OR Members.MemberID = Game.P15
OR Members.MemberID = Game.P16
GROUP BY Members.NickName, Game.GameName, Game.gameID
ORDER BY NickName ASC
</cfquery>
Please help!
You can see the an example of the output at:
http://cf.bigfatdesigns.com/clients/poker/leaderboard.cfm
THANK YOU!
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Find out how to get a fax number that sends and receives faxes using your
current email address
http://www.houseoffusion.com/banners/view.cfm?bannerid=64
Message: http://www.houseoffusion.com/lists.cfm/link=i:15:548
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/15
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:15
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
