Problem in Calculating Total Score in a Quiz Database

[KushKashyap]

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

[Ramy Mahrous]

haaaa, give me your tryings friend :)

[KushKashyap]

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

[Ramy Mahrous]

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

[KushKashyap]

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.

[Ramy Mahrous]

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

[KushKashyap]

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

[KushKashyap]

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.

[Ramy Mahrous]

You don't have column "UserName"

[KushKashyap]

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

[KushKashyap]

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

[Ramy Mahrous]

Are you sure you using correct database?

[KushKashyap]

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

[Sakthimeenakshi]

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