| Author |
 Topic  |
|
kwikwisi
Constraint Violating Yak Guru
278 Posts |
 Posted - 04/10/2008 : 04:21:46
|
hi
Now i want to count Chk when chk=2
my 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 uneval
But it always show same as no:of problemdescription .
Plz tell me how can i modify? |
|
|
elancaster
A very urgent SQL Yakette
United Kingdom
1208 Posts |
Posted - 04/10/2008 : 04:25:12
|
sum(case when Chk = 2 then 1 else 0 end)
Em |
 |
|
|
kwikwisi
Constraint Violating Yak Guru
278 Posts |
Posted - 04/10/2008 : 04:31:53
|
it works well
Thanks.
|
|
|
|
kwikwisi
Constraint Violating Yak Guru
278 Posts |
Posted - 04/10/2008 : 04:52:23
|
i also want to count for incorrect answer when chk=0
my 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 well
plz help me again! |
|
|
|
elancaster
A very urgent SQL Yakette
United Kingdom
1208 Posts |
Posted - 04/10/2008 : 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
278 Posts |
Posted - 04/10/2008 : 05:09:10
|
hi
Thanks 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
United Kingdom
1208 Posts |
|
|
elancaster
A very urgent SQL Yakette
United Kingdom
1208 Posts |
Posted - 04/10/2008 : 05:15:26
|
i'd guess it'll be something like...
select u.username
from sampleuserans a
join UserTable u on a.userid = u.userid
Em |
|
|
|
kwikwisi
Constraint Violating Yak Guru
278 Posts |
Posted - 04/10/2008 : 05:18:59
|
Usertable(userid,username)
sampleUsertable(userid,--,--)
My View is like that:
CREATE PROCEDURE [dbo].[view1]
AS
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
from sampleUserAns
group by UserID
GO
I want to get username from Usertable based on userid from sampleUserAns in this procedure.
Plz help me! |
|
|
|
elancaster
A very urgent SQL Yakette
United Kingdom
1208 Posts |
Posted - 04/10/2008 : 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 incorrect
from sampleUserAns a
join UserTable u on a.userid = u.userid
group by u.UserID,username
Em |
|
|
|
kwikwisi
Constraint Violating Yak Guru
278 Posts |
Posted - 04/10/2008 : 05:26:50
|
hi
There'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.UserID
What's wrong? |
|
|
|
elancaster
A very urgent SQL Yakette
United Kingdom
1208 Posts |
Posted - 04/10/2008 : 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 |
Edited by - elancaster on 04/10/2008 05:32:36 |
|
|
|
kwikwisi
Constraint Violating Yak Guru
278 Posts |
Posted - 04/10/2008 : 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
United Kingdom
1208 Posts |
Posted - 04/10/2008 : 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 message
Em |
Edited by - elancaster on 04/10/2008 05:49:59 |
|
|
|
kwikwisi
Constraint Violating Yak Guru
278 Posts |
Posted - 04/10/2008 : 06:01:02
|
My tables are:
sampleUser(UserID,UserName)
sampleUserAns(UserID,----)
Procedure is:
CREATE PROCEDURE [dbo].[sp_overall1] AS
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 incorrect
from sampleUserAns a join sampleUser u on a.UserID = u.UserID
group by u.UserID,UserName
GO
Now 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
United Kingdom
1208 Posts |
Posted - 04/10/2008 : 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
278 Posts |
Posted - 04/10/2008 : 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
United Kingdom
1208 Posts |
Posted - 04/11/2008 : 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
278 Posts |
Posted - 04/11/2008 : 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
United Kingdom
1208 Posts |
Posted - 04/11/2008 : 03:25:57
|
for a difference problem you'd be best to start a new thread
Em |
|
|
|
kwikwisi
Constraint Violating Yak Guru
278 Posts |
Posted - 04/11/2008 : 03:50:39
|
hi,
thanks for ur all help.
Plz let me know is it impossible to retrieve username from sampleUser? |
|
|
|
Topic |
|
Count Problem
