What would be the best way to do this

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 2005 Forums

Transact-SQL (2005)

What would be the best way to do this

Author

Topic

lucsky8
Posting Yak Master

105 Posts

Posted - 2009-02-19 : 08:27:55

Hi,

I need to count how many a,b,c and d per question there is.
The question are dynamic so it possible to have multiple question,
And a question that have no a,b,c or d i need for that letter to show 0

This query return this

SELECT	upper(te.strNote), 
		1 as ctr, 
		tee.intEvaluationElementId 
from       tblM2EvaluationElementReponse as te 
INNER JOIN tblM2EvaluationElement as tee  
ON         tee.intEvaluationElementId = te.intEvaluationElementId  
WHERE      tee.intEvaluationId = 50 
AND        te.strNote <> ''
ORDER BY   tee.intEvaluationElementId,intElevesId

B 1 79
B 1 79
B 1 79
B 1 79
B 1 79
B 1 79
B 1 79
A 1 80
A 1 80
A 1 80
A 1 80
A 1 80
A 1 80
A 1 80
A 1 80
C 1 80
A 1 80

79 and 80 is the id of the question what i am doing right now.

DECLARE @Temp table
(
	strNote varchar(50),
	ctr	  int,
	strQues varchar(50) 
)

insert into @Temp
SELECT	upper(te.strNote), 
		1 as ctr, 
		tee.intEvaluationElementId 
from       tblM2EvaluationElementReponse as te 
INNER JOIN tblM2EvaluationElement as tee  
ON         tee.intEvaluationElementId = te.intEvaluationElementId  
WHERE      tee.intEvaluationId = 50 
AND        te.strNote <> ''
ORDER BY   tee.intEvaluationElementId,intElevesId 


SELECT strQues,strNote, SUM(ctr)
FROM @Temp
GROUP BY strQues,strNote
order by strQues,strNote

That will give this

79 B 7
80 A 9
80 C 1

Now that almost right.

I need to figure out how to put d = 0 if there no D etc

So it will look like this

79 A 0
79 B 7
79 C 0
79 D 0
80 A 9
80 B 0
80 C 1
80 D 0

Any idea?
Tks
Luc

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-19 : 08:48:04

This works ?

select a.strQues,t.a,ctr=case when a.strNote=t.a then a.ctr else 0 end
from(
SELECT strQues,strNote, SUM(ctr) ctr
FROM @Temp
GROUP BY strQues,strNote
order by strQues,strNote )a  cross apply (select 'a' a union select 'b' union select 'c' union select 'd')t

lucsky8
Posting Yak Master

105 Posts

Posted - 2009-02-19 : 09:05:16

hi, tks for the reply
it almost working

if i run this

select a.strQues,t.a,ctr=case when a.strNote=t.a then a.ctr else 0 end
from(
SELECT  strQues,strNote, SUM(ctr) ctr
FROM @Temp
GROUP BY strQues,strNote
)a  
cross apply (select 'a' a union select 'b' union select 'c' union select 'd')t
order by strQues,strNote

i get this

79 a 0
79 b 7
79 c 0
79 d 0
80 c 0
80 d 0
80 a 9
80 b 0
80 a 0
80 c 1
80 b 0
80 d 0

Is there a way to remove the dupliacte in the id 80
80 a 9
80 a 0

lucsky8
Posting Yak Master

105 Posts

Posted - 2009-02-19 : 09:11:54

I change your query

select a.strQues,t.a,ctr=case when a.strNote=t.a then a.ctr else 0 end
from(
SELECT strQues,strNote, SUM(ctr) ctr
FROM @Temp
GROUP BY strQues,strNote
order by strQues,strNote )a  cross apply (select 'a' a union select 'b' union select 'c' union select 'd')t

select a.strQues,t.a,ctr=case when a.strNote=t.a then a.ctr else 0 end
from(
SELECT  strQues,strNote, SUM(ctr) ctr
FROM @Temp
GROUP BY strQues,strNote
)a  
cross apply (select 'a' a union select 'b' union select 'c' union select 'd')t
order by strQues,strNote

because it give me an error

Msg 1033, Level 15, State 1, Line 32
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
Msg 102, Level 15, State 1, Line 33
Incorrect syntax near 't'.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-19 : 09:35:35

Ignore what I gave you previously, Try this.

select abc.strQues,abc.strNote,CTR=ISNULL(xyz.ctr,0) from 
(
SELECT   distinct  a.strQues,strNote=t.a 
FROM @temp a
cross apply (select 'a' a union select 'b' union select 'c' union select 'd')t  
)abc
left join (SELECT strQues,strNote, SUM(ctr) ctr
FROM @temp
GROUP BY strQues,strNote
) xyz
on xyz.strQues=abc.strQues and xyz.strNote=abc.strNote

lucsky8
Posting Yak Master

105 Posts

Posted - 2009-02-19 : 09:39:43

quote:

Originally posted by sakets_2000

Ignore what I gave you previously, Try this.

select abc.strQues,abc.strNote,CTR=ISNULL(xyz.ctr,0) from 
(
SELECT   distinct  a.strQues,strNote=t.a 
FROM @temp a
cross apply (select 'a' a union select 'b' union select 'c' union select 'd')t  
)abc
left join (SELECT strQues,strNote, SUM(ctr) ctr
FROM @temp
GROUP BY strQues,strNote
) xyz
on xyz.strQues=abc.strQues and xyz.strNote=abc.strNote

Wow tks it working great i don't know how you guys can create query so easaly!!

your are genuis!!!
many tks!

Luc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-19 : 09:45:26

this will work as long as you've only types upto D. what will happen if a new category E comes? where exactly do you have information on category like A,B,C,..

lucsky8
Posting Yak Master

105 Posts

Posted - 2009-02-19 : 09:50:19

quote:
Originally posted by visakh16

this will work as long as you've only types upto D. what will happen if a new category E comes? where exactly do you have information on category like A,B,C,..

Hi good point!

This program is for a school District and it only possible to have a,b,c or d on a test.

When the teacher enter the score there only able to enter a,b,c or d.
And trust me i make sure with the District that only a,b,c and d should be enter.

If the change there mind that have to change it a lot of place not only in my program!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-19 : 09:55:09

nope. you can make a scalable solution which works regardless of number of categries that exist and they keep on adding. what you need is to determine which table you store the category values.

lucsky8
Posting Yak Master

105 Posts

Posted - 2009-02-19 : 10:00:18

Right now i only have 3 table there more table for the student information and teacher

tblM2Evaluation
tblM2EvaluationElement
tblM2EvaluationElementReponse

1 = name of the evaluation
2 = name of the question and there no limit of questions
3 = score, wich question and the student id

I don't have a table for categorie."A,B,C,D"

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-19 : 10:06:25

You have column strNote in tblM2EvaluationElementReponse where from you get categories, don't you ? Do you want your result to include all distinct categories in that column, or just a,b,c,d ? If you are confident it'll always be either a,b,c or d, then the query I gave you earlier should be fine.

lucsky8
Posting Yak Master

105 Posts

Posted - 2009-02-19 : 10:07:38

quote:
Originally posted by sakets_2000

You have column strNote in tblM2EvaluationElementReponse where from you get categories, don't you ? Do you want your result to include all distinct categories in that column, or just a,b,c,d ? If you are confident it'll always be either a,b,c or d, then the query I gave you earlier should be fine.

Tks for your concern i'am pretty confident that it will stay a,b,c and d only!

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-19 : 10:08:43

great !

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-19 : 10:09:31

if thats the case, you could simply tweak code like this and it will work fine regardless of number of categories present


select abc.strQues,abc.strNote,CTR=ISNULL(xyz.ctr,0) from 
(
SELECT   distinct  a.strQues,strNote=t.a 
FROM @temp a
cross join (select distinct strNote as a from tblM2EvaluationElementReponse)t  
)abc
left join (SELECT strQues,strNote, SUM(ctr) ctr
FROM @temp
GROUP BY strQues,strNote
) xyz
on xyz.strQues=abc.strQues and xyz.strNote=abc.strNote

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-19 : 10:19:24

quote:
Originally posted by visakh16

if thats the case, you could simply tweak code like this and it will work fine regardless of number of categories present

That might not work if

select distinct strNote as a from tblM2EvaluationElementReponse

doesn't have rows having codes ,A,B,C,D, for some reason.

lucsky8
Posting Yak Master

105 Posts

Posted - 2009-02-19 : 10:32:39

quote:
Originally posted by sakets_2000

quote:
Originally posted by visakh16

if thats the case, you could simply tweak code like this and it will work fine regardless of number of categories present

That might not work if
select distinct strNote as a from tblM2EvaluationElementReponse

doesn't have rows having codes ,A,B,C,D, for some reason.

Your query work fine i try it and it exactly what i want!
Yes a,b,c,d is there for a resaon

it a score
Take it like translate in number

A = 85% and more
B = 75% to 84.99%
C = 65% to 74.99
D = less theb 65%

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-19 : 11:30:12

quote:
Originally posted by sakets_2000

quote:
Originally posted by visakh16

if thats the case, you could simply tweak code like this and it will work fine regardless of number of categories present

That might not work if
select distinct strNote as a from tblM2EvaluationElementReponse

doesn't have rows having codes ,A,B,C,D, for some reason.

it will have all categories provides its the master table for categories. i dont know which table is master table so just followed from your post

lucsky8
Posting Yak Master

105 Posts

Posted - 2009-02-20 : 08:19:39

Tks for your help!

I have one last favor to ask!

Is it possible to do a pivot on this?

select abc.strNote,CTR=ISNULL(xyz.ctr,0)
from 
(
	SELECT distinct  a.strQues,strNote=t.a 
	FROM @temp a
	cross apply (select 'a' a union select 'b' union select 'c' union select 'd')t  
	)abc
left join (SELECT strQues,strNote, SUM(ctr) ctr
           FROM @temp
           GROUP BY strQues,strNote
) xyz
on xyz.strQues=abc.strQues and xyz.strNote=abc.strNote

Instead of giving me this :
a 0
b 7
c 0
d 0
a 9
b 0
c 1
d 0
a 4
b 0
c 1
d 0
a 4
b 0
c 2
d 1

i would have

strNote Q1 Q2 Q3 Q4
A 0 9 4 4
B 7 0 0 0
C 0 1 1 2
D 0 0 0 1

The number of Q is dynamic "could be 3, 5 8 etc"
But it always just a,b,c and d

I use pivot before but not with a query that complicated!
Tks again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 09:28:48

what are q1,q2,... are they categories?

lucsky8
Posting Yak Master

105 Posts

Posted - 2009-02-20 : 09:34:50

quote:
Originally posted by visakh16

what are q1,q2,... are they categories?

Hi
Q1 = question 1
Q2 = question 2
etc

The number of question is dynamic
For a test it could be 2 questions another 5 etc

The query i have return how many a,b,c and d in q1 and q2 etc

I need to do a pivot on q1,q2 etc
The number qX is unknown

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 09:38:02

which field contain question info?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources