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

Re: Problem in Calculating Total Score in a Quiz Database 80 80

haaaa, give me your tryings friend :)

Re: Problem in Calculating Total Score in a Quiz Database 80 80

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

Re: Problem in Calculating Total Score in a Quiz Database 80 80

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!!

Re: Problem in Calculating Total Score in a Quiz Database 80 80

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.

Re: Problem in Calculating Total Score in a Quiz Database 80 80

You're welcome, I'm happy you solved your problem, please mark it as solved.

Re: Problem in Calculating Total Score in a Quiz Database 80 80

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:-(

Re: Problem in Calculating Total Score in a Quiz Database 80 80

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.

Re: Problem in Calculating Total Score in a Quiz Database 80 80

You don't have column "UserName"

Re: Problem in Calculating Total Score in a Quiz Database 80 80

I do have..You can check my table screenshot at the top

Re: Problem in Calculating Total Score in a Quiz Database 80 80

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

Re: Problem in Calculating Total Score in a Quiz Database 80 80

Are you sure you using correct database?

Re: Problem in Calculating Total Score in a Quiz Database 80 80

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
Re: Problem in Calculating Total Score in a Quiz Database 80 80

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.