SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Count Problem
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

kwikwisi
Constraint Violating Yak Guru

281 Posts

Posted - 04/10/2008 :  04:21:46  Show Profile  Reply with Quote
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  Show Profile  Reply with Quote
sum(case when Chk = 2 then 1 else 0 end)

Em
Go to Top of Page

kwikwisi
Constraint Violating Yak Guru

281 Posts

Posted - 04/10/2008 :  04:31:53  Show Profile  Reply with Quote
it works well
Thanks.
Go to Top of Page

kwikwisi
Constraint Violating Yak Guru

281 Posts

Posted - 04/10/2008 :  04:52:23  Show Profile  Reply with Quote
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!
Go to Top of Page

elancaster
A very urgent SQL Yakette

United Kingdom
1208 Posts

Posted - 04/10/2008 :  05:01:37  Show Profile  Reply with Quote
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
Go to Top of Page

kwikwisi
Constraint Violating Yak Guru

281 Posts

Posted - 04/10/2008 :  05:09:10  Show Profile  Reply with Quote
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?
Go to Top of Page

elancaster
A very urgent SQL Yakette

United Kingdom
1208 Posts

Posted - 04/10/2008 :  05:12:50  Show Profile  Reply with Quote
so you'll need to join these tables together. post up some data about the tables and we can help you with the exact syntax.

see this for a guide on what to post...
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Em
Go to Top of Page

elancaster
A very urgent SQL Yakette

United Kingdom
1208 Posts

Posted - 04/10/2008 :  05:15:26  Show Profile  Reply with Quote
i'd guess it'll be something like...

select u.username
from sampleuserans a
  join UserTable u on a.userid = u.userid




Em
Go to Top of Page

kwikwisi
Constraint Violating Yak Guru

281 Posts

Posted - 04/10/2008 :  05:18:59  Show Profile  Reply with Quote
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!
Go to Top of Page

elancaster
A very urgent SQL Yakette

United Kingdom
1208 Posts

Posted - 04/10/2008 :  05:25:58  Show Profile  Reply with Quote
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
Go to Top of Page

kwikwisi
Constraint Violating Yak Guru

281 Posts

Posted - 04/10/2008 :  05:26:50  Show Profile  Reply with Quote
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?
Go to Top of Page

elancaster
A very urgent SQL Yakette

United Kingdom
1208 Posts

Posted - 04/10/2008 :  05:30:11  Show Profile  Reply with Quote
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
Go to Top of Page

kwikwisi
Constraint Violating Yak Guru

281 Posts

Posted - 04/10/2008 :  05:43:29  Show Profile  Reply with Quote
i don't want to create view.
plz tell me how my procedure should be to meet my target?

Thanks.
Go to Top of Page

elancaster
A very urgent SQL Yakette

United Kingdom
1208 Posts

Posted - 04/10/2008 :  05:49:30  Show Profile  Reply with Quote
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
Go to Top of Page

kwikwisi
Constraint Violating Yak Guru

281 Posts

Posted - 04/10/2008 :  06:01:02  Show Profile  Reply with Quote
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"
Go to Top of Page

elancaster
A very urgent SQL Yakette

United Kingdom
1208 Posts

Posted - 04/10/2008 :  06:06:53  Show Profile  Reply with Quote
does the query itself bring back the results you want?

where are you running the code to create the proc?

Em
Go to Top of Page

kwikwisi
Constraint Violating Yak Guru

281 Posts

Posted - 04/10/2008 :  23:46:00  Show Profile  Reply with Quote
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.
Go to Top of Page

elancaster
A very urgent SQL Yakette

United Kingdom
1208 Posts

Posted - 04/11/2008 :  01:31:15  Show Profile  Reply with Quote
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
Go to Top of Page

kwikwisi
Constraint Violating Yak Guru

281 Posts

Posted - 04/11/2008 :  01:55:29  Show Profile  Reply with Quote
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!!!!!!
Go to Top of Page

elancaster
A very urgent SQL Yakette

United Kingdom
1208 Posts

Posted - 04/11/2008 :  03:25:57  Show Profile  Reply with Quote
for a difference problem you'd be best to start a new thread

Em
Go to Top of Page

kwikwisi
Constraint Violating Yak Guru

281 Posts

Posted - 04/11/2008 :  03:50:39  Show Profile  Reply with Quote
hi,
thanks for ur all help.
Plz let me know is it impossible to retrieve username from sampleUser?
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.25 seconds. Powered By: Snitz Forums 2000