I am making a SQL query to display a high score list of several users scores per user, that also should give a avarage stars the specific user have achived, and the MAX level reached, and also I would like to make a calculation of the total time the user has spent in the game by summing all the time per levels..
I have the following issues:
1 : My SQL Query does not give me one line per user.
2 : The sums that are ginving is not per user but a sum of all users.
3 : The avarage STARS are not displaying with decimal.
4 : I cannot get the "score.time_spent" to calculate the sum of all the time the specific user has been playing
My SQL Query at pressent time is looking like this:
SELECT DISTINCT
game_users.username AS 'User Name',
'Level Reached' = (SELECT DISTINCT MAX(score.game_level) FROM score
WHERE score.game_users_id IN
(SELECT game_users_id FROM game_users
WHERE game_users.id = score.game_users_id)),
score.time_spent AS 'Total Time Spent',
'Stars' = (SELECT DISTINCT AVG(score.stars) FROM score
WHERE score.game_users_id IN
(SELECT game_users_id FROM game_users
WHERE game_users.id = score.game_users_id)),
'High Score' = (SELECT DISTINCT SUM(score.game_level_score) FROM score
WHERE score.game_users_id IN
(SELECT game_users_id FROM game_users
WHERE game_users.id = score.game_users_id)),
game_users.current_state AS 'Online State'
FROM
game_users
INNER JOIN score ON game_users.id = score.game_users_id
GROUP BY
game_users.id,
game_users.username,
score.game_level_score,
score.game_level,
score.time_spent,
score.stars,
game_users.current_state
GO
The "game_users" table looks like this:
id int Unchecked
firstname varchar(100) Checked
lastname varchar(100) Checked
email varchar(100) Checked
username varchar(50) Checked
usr_pass varchar(50) Checked
current_state int Checked
The "score" table looks like this:
id int Unchecked
game_users_id int Unchecked
game_level varchar(4) Checked
game_level_score numeric(18, 0) Checked
stars int Checked
time_spent time(7) Checked
Can someone help me out in this mess? :-)
Best Regards
Stig :-)
Looks like this should solve it:
SELECT a.username AS 'User Name'
, MAX(b.game_level) AS 'Level Reached'
, CAST(DATEADD(millisecond,SUM(DATEDIFF(millisecond,0,CAST(b.time_spent AS DATETIME))),0) AS TIME) AS 'Total Time Spent'
, AVG(CAST(b.stars AS FLOAT)) AS 'Stars'
, SUM(b.game_level_score) AS 'High Score'
, a.current_state AS 'Online State'
FROM game_users a
JOIN score b
ON a.id = b.game_users_id
GROUP BY a.username, a.current_state
Note: the CAST line is from this site - http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=121027
EDIT: Casted stars as float.
0 comments:
Post a Comment