Count Problem - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Count Problem

Author

Topic

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-04-10 : 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

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 well
Thanks.

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-04-10 : 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

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

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

1208 Posts

Posted - 2008-04-10 : 05:12:50

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

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.username
from sampleuserans a
  join UserTable u on a.userid = u.userid

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

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 incorrect
from sampleUserAns a
  join UserTable u on a.userid = u.userid
group by u.UserID,username

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-04-10 : 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

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 message

Em

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

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

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 thread

Em

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources