SQL Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Query

Author

Topic

kush
Starting Member

4 Posts

Posted - 2009-02-06 : 13:12:58

I have this table:
UserQuizID(int) PK
QuizID(int)
DateTimeComplete(datetime)
Score(int)
UserName(nvarchar)

Now I want to calculate the total score of all the quizzes taken by a person. Since a single user can take a particular quiz more than once, I want to count the score of that quiz for that person only once..when he first time takes that quiz. And I want to group these results by UserName.

How can I do it??

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-06 : 13:20:10

Maybe:

Select UserName,QuizID,Sum(Score) as TotalScores
from table
Group by UserName,QuizID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-07 : 08:01:42

[code]SELECT t.UserName,SUM(Score) AS TotalQuizScore
FROM Table t
INNER JOIN (SELECT UserName,QuizID, MIN(DateTimeComplete) AS First
FROM UserName,QuizID)tmp
ON t.UserName=tmp.UserName
AND t.QuizID=tmp.QuizID
AND t.DateTimeComplete=tmp.First
GROUP BY t.UserName
[/code]

kush
Starting Member

4 Posts

Posted - 2009-02-10 : 06:11:27

Thanks visakh16 and sodeep. I greatly appreciate your help.

visakh16 i am getting one problem with your query.. I am getting a "Invalid object name 'UserName'." error ..

I dont have any clue that why i am getting this error. I am using your SQL query almost as it was


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

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

ON t.UserName = tmp.UserName 

AND t.QuizID = tmp.QuizID 

AND t.DateTimeComplete = tmp.First 

GROUP BY t.UserName

If i try VS SqlDatasource wizard in VS i get a similar query


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

Thanks in advance

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-02-10 : 06:14:59

think it might be a typo. here


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

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

ON t.UserName = tmp.UserName 

AND t.QuizID = tmp.QuizID 

AND t.DateTimeComplete = tmp.First 

GROUP BY t.UserName

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

kush
Starting Member

4 Posts

Posted - 2009-02-10 : 09:35:05

No sir i checked it...It is still not working.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-10 : 09:40:37

try this mkodified one:-



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

kush
Starting Member

4 Posts

Posted - 2009-02-10 : 16:18:21

Thanks visakh16 , the issue is resolved.

thanks a lot for solving this problem. The query is running flawlessly and as expected.

i am also thankful to all other members for helping me out.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-10 : 22:48:40

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources