SQL Query Gold Check

[Ninja300]

Simple Gold check via SQL if you don't have a web panel.

Copy, Paste & Execute

USE RanGame1

--------------gold per school-----------------------------------------------------------------
SELECT CASE WHEN chaschool=0 THEN 'Sacred Gate'
WHEN chaschool=1 THEN 'Mystic Peak'
WHEN chaschool=2 THEN 'Phoenix'
END AS School, REPLACE(CONVERT(VARCHAR(50), SUM((CAST(chainfo.ChaMoney AS money))), 1), '.00', '')  as GoldSchool
FROM ChaInfo
WHERE ChaSchool < 3
GROUP by ChaSchool
ORDER by SUM(Chainfo.ChaMoney) desc

--------------gold per char-----------------------------------------------------------------

SELECT CASE WHEN ChaSchool=0 THEN 'Sacred Gate'
WHEN ChaSchool=1 THEN 'Mystic Peak'
WHEN ChaSchool=2 THEN 'Phoenix'END AS School,ChaName, REPLACE(CONVERT(VARCHAR(50), SUM((CAST(ChaInfo.ChaMoney AS money))), 1), '.00', '')  as GoldChar
FROM ChaInfo
WHERE UserNum in (SELECT UserNum from RanUser.dbo.UserInfo WHERE  UserType =1) and ChaMoney >0 and ChaSchool < 3
GROUP BY chaname,ChaSchool
ORDER BY SUM(Chainfo.ChaMoney) desc


--------------gold per guild-----------------------------------------------------------------

SELECT GuildInfo.GuNum, GuildInfo.GuName , REPLACE(CONVERT(VARCHAR(50), SUM((CAST(chainfo.ChaMoney AS money))), 1), '.00', '')  as GoldGuild
FROM GuildInfo
INNER JOIN ChaInfo
ON GuildInfo.GuNum = ChaInfo.GuNum
WHERE UserNum in (select UserNum from ranuser.dbo.UserInfo where  UserType=1)
GROUP by GuildInfo.GuNum,GuildInfo.GuName
ORDER by SUM(Chainfo.ChaMoney) desc

--------------gold per username--------------------------- --------------------------------------
USE RanUser
SELECT UserInfo.UserNum, UserInfo.UserName, REPLACE(CONVERT(VARCHAR(50), SUM((CAST(Chainfo.ChaMoney AS money))), 1), '.00', '') as GoldUser
FROM RanUser.dbo.UserInfo
INNER JOIN RanGame1.dbo.ChaInfo
ON Userinfo.UserNum = ChaInfo.UserNum 
WHERE UserType=1GROUP BY UserInfo.UserNum, UserInfo.UserName
ORDER BY SUM (Chainfo.ChaMoney) DESC

 

[lncy9909]

Thanks for sharing