Author |
Topic |
kwikwisi
Constraint Violating Yak Guru
283 Posts |
Posted - 2008-04-10 : 04:21:46
|
hiNow i want to count Chk when chk=2my query is like that:select UserID,count(ProblemDescription) as noques,sum(case when Chk<>2 then Chk else 0 end) as score,count(case when Chk<>1 then Chk else 0 end) as unevalBut it always show same as no:of problemdescription .Plz tell me how can i modify? |
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2008-04-10 : 04:25:12
|
sum(case when Chk = 2 then 1 else 0 end)Em |
|
|
kwikwisi
Constraint Violating Yak Guru
283 Posts |
Posted - 2008-04-10 : 04:31:53
|
it works wellThanks. |
|
|
kwikwisi
Constraint Violating Yak Guru
283 Posts |
Posted - 2008-04-10 : 04:52:23
|
i also want to count for incorrect answer when chk=0my query is"sum(case when Chk<>2 then Chk else 0 end) as score,sum(case when Chk = 2 then 1 else 0 end) as uneval,sum(case when Chk = 0 then 2 else 1 end) as incorrect"but it doesn't work wellplz help me again! |
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2008-04-10 : 05:01:37
|
do you just want to count it? don't understand why you've put 'then 2 else 1'. is it not just...sum(case when Chk = 0 then 1 else 0 end)Em |
|
|
kwikwisi
Constraint Violating Yak Guru
283 Posts |
Posted - 2008-04-10 : 05:09:10
|
hiThanks again for your warm help.Actually i'm just beginner in sql query.I also want to get username from User table base on userid in sampleuserans .Is it possible? |
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2008-04-10 : 05:15:26
|
i'd guess it'll be something like...select u.usernamefrom sampleuserans a join UserTable u on a.userid = u.userid Em |
|
|
kwikwisi
Constraint Violating Yak Guru
283 Posts |
Posted - 2008-04-10 : 05:18:59
|
Usertable(userid,username)sampleUsertable(userid,--,--)My View is like that:CREATE PROCEDURE [dbo].[view1] ASselect UserID,count(ProblemDescription) as noques,sum(case when Chk<>2 then Chk else 0 end) as score,sum(case when Chk = 2 then 1 else 0 end) as uneval,sum(case when Chk = 0 then 1 else 0 end) as incorrectfrom sampleUserAnsgroup by UserIDGOI want to get username from Usertable based on userid from sampleUserAns in this procedure.Plz help me! |
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2008-04-10 : 05:25:58
|
firstly... you're creating a procedure. is that really what you want? or do you just want to create a view? (as you've named it)select u.UserID, UserName, count(ProblemDescription) as noques, sum(case when Chk<>2 then Chk else 0 end) as score, sum(case when Chk = 2 then 1 else 0 end) as uneval, sum(case when Chk = 0 then 1 else 0 end) as incorrectfrom sampleUserAns a join UserTable u on a.userid = u.useridgroup by u.UserID,username Em |
|
|
kwikwisi
Constraint Violating Yak Guru
283 Posts |
Posted - 2008-04-10 : 05:26:50
|
hiThere's no username in sampleuserans.I've got incorrect syntax error.my query :select UserID,count(ProblemDescription) as noques,sum(case when Chk<>2 then Chk else 0 end) as score,sum(case when Chk = 2 then 1 else 0 end) as uneval,sum(case when Chk = 0 then 1 else 0 end) as incorrect,u.UserName from sampleUserAns a join sampleUser u on a.UserID = u.UserIDWhat's wrong? |
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2008-04-10 : 05:30:11
|
you said your user table was called usertable... but you've put sampleuser in your query?and sampleusertable ...should that be sampleuserans as in your query?Em |
|
|
kwikwisi
Constraint Violating Yak Guru
283 Posts |
Posted - 2008-04-10 : 05:43:29
|
i don't want to create view.plz tell me how my procedure should be to meet my target?Thanks. |
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2008-04-10 : 05:49:30
|
check your table names, they are not consistent through your posts. the run the query i posted above. if it doesn't work, post back the complete query that you ran and the error messageEm |
|
|
kwikwisi
Constraint Violating Yak Guru
283 Posts |
Posted - 2008-04-10 : 06:01:02
|
My tables are:sampleUser(UserID,UserName)sampleUserAns(UserID,----)Procedure is:CREATE PROCEDURE [dbo].[sp_overall1] ASselect u.UserID, UserName, count(ProblemDescription) as noques, sum(case when Chk<>2 then Chk else 0 end) as score, sum(case when Chk = 2 then 1 else 0 end) as uneval, sum(case when Chk = 0 then 1 else 0 end) as incorrectfrom sampleUserAns a join sampleUser u on a.UserID = u.UserIDgroup by u.UserID,UserNameGONow i can't create procedure and error msg is:"The name specified in Text property's 'create...' statement must match the Name property and must be followed by valid TSQL atements" |
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2008-04-10 : 06:06:53
|
does the query itself bring back the results you want?where are you running the code to create the proc?Em |
|
|
kwikwisi
Constraint Violating Yak Guru
283 Posts |
Posted - 2008-04-10 : 23:46:00
|
i use sql2000 and vb.net Actually i query for ongoing test .users can take test and save in table and then they can recall test that they haven't taken. i take long time for this procedure but i can't still get correct solution. |
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2008-04-11 : 01:31:15
|
ok, but the answers to these questions....?quote: Originally posted by elancaster does the query itself bring back the results you want?where are you running the code to create the proc?Em
Em |
|
|
kwikwisi
Constraint Violating Yak Guru
283 Posts |
Posted - 2008-04-11 : 01:55:29
|
ok,i'll solve this user name problem later.Now my important problem is to handle relation between table,view and parameter for ongoing test.I want ProID,ProDescription from samplePro table,the ProID need to different from ProID in view(vw_temppro),then userid in vw_temppro must same the parameter brought from previous page.Any help!!!!!! |
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2008-04-11 : 03:25:57
|
for a difference problem you'd be best to start a new threadEm |
|
|
kwikwisi
Constraint Violating Yak Guru
283 Posts |
Posted - 2008-04-11 : 03:50:39
|
hi,thanks for ur all help.Plz let me know is it impossible to retrieve username from sampleUser? |
|
|
Next Page
|