0

Hello friends, i am facing a bit of difficulty in designing a SQL query for calculating the total score of all the quizes taken by users in my quiz application to create a sort of leaderboard or ranking system.

My table have following tables:
UserQuizID (int) - PK
QuizID (int)
DateTimeComplete (smalldatetime)
Score (tinyint)
Username (nvarchar(256))

Now the problem is that a user can take a particular quiz many times over but i want to consider the score of only the first time a user takes a particluar quiz. I think we can do this by taking that score which has the minimum DateTimeComplete value for the given QuizID and the Username.

I just want to create a leader board system in which i can display the ranking of my users according to their total score in the quizzes taken by them.

Thanks in advance...any help will be greatly appreciated

Attachments TableScreenshot.JPG 40.45 KB
3
Contributors
13
Replies
14
Views
8 Years
Discussion Span
Last Post by Sakthimeenakshi
0

Hi RamyMahrous,
i was tryng the following SQL statement, this is the best i am able to do... SELECT Username, SUM(DISTINCT Score) FROM UserQuiz GROUP BY QuizID , Username
i tried something like this
SELECT Username, SUM(DISTINCT Score) FROM UserQuiz GROUP BY QuizID , Username having min(DateTimeComplete)
but this is giving me error...

What i want is that my results are displayed in a manner that the user with highest total for all the quiz is at top. but i want to count the score for one quiz only only once..not multiple times..i want to restrict it on the basis of minimum value of the DateTimeComplete

0

First of all your database design isn't correct, or you've another opinion? discuss that with me..
I don't need to waste time in writing SQL Statement on invalid database design, your query isn't hard plus I don't know why you used distinct in your solution as you just need the first time the user took the exam!!

0

Thanks RamyMahrous. You were right, i was using the wrong approach..i shouldnt have used distinct as i just want the score of the user when he first took the test, and then add them up to get his total..but as you can see i am not very good with SQL:-( just a starter you can say,who can write simple queries:-).
And ya RamyMahrous its also not possible for me to change the database schema.
Thanks for your help, I greatly appreciate.

0

nope dear i still have not solved it yet :-) but i did apply some more brain , but still could not get the results. m still trying:-(

0

i tried this but this also isnt working

SELECT t.UserName, SUM(t.Score) AS TotalQuizScore FROM UserQuiz AS t
 INNER JOIN (SELECT t.UserName, t.QuizID, MIN(t.DateTimeComplete) AS First FROM UserName 
CROSS JOIN QuizID) AS tmp ON t.UserName = tmp.UserName 
AND t.QuizID = tmp.QuizID 
AND t.DateTimeComplete = tmp.First GROUP BY t.UserName

I am getting a "Invalid object name 'UserName'." error.

0

Sorry i had misspelled it in my first post. But my table do have a UserName column as you can see in my screenshot..

0

This is the final query

SELECT t.UserName, SUM(Score) AS TotalQuizScore 
FROM UserQuiz  t

 INNER JOIN (SELECT UserName, QuizID, 
MIN(DateTimeComplete) AS First 
FROM UserQuiz
GROUP BY UserName , QuizID)  tmp 

ON t.UserName = tmp.UserName 

AND t.QuizID = tmp.QuizID 

AND t.DateTimeComplete = tmp.First 

GROUP BY t.UserName
0

Hi KushKashyap;798084,

I found the query for the condition gave by you...

Pls try the following query....

Select a.username,a.QUIZID,a.DATETIMECOMPLETE,a.SCORE 
from quiz a
inner join (Select b. username,b.quizid,min(b.datetimecomplete)as mdatetimecomplete from Quiz b
group by b. username,b.quizid)ss
on a. username = ss. username and a.quizid = ss.quizid and a.Datetimecomplete = ss.mdatetimecomplete 
order by a.score desc

Thanks & Regards
Sakthimeenakshi.S


Hello friends, i am facing a bit of difficulty in designing a SQL query for calculating the total score of all the quizes taken by users in my quiz application to create a sort of leaderboard or ranking system.

My table have following tables:
UserQuizID (int) - PK
QuizID (int)
DateTimeComplete (smalldatetime)
Score (tinyint)
Username (nvarchar(256))

Now the problem is that a user can take a particular quiz many times over but i want to consider the score of only the first time a user takes a particluar quiz. I think we can do this by taking that score which has the minimum DateTimeComplete value for the given QuizID and the Username.

I just want to create a leader board system in which i can display the ranking of my users according to their total score in the quizzes taken by them.

Thanks in advance...any help will be greatly appreciated

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.