Count horizontally

[borgkling2003]

I got a database that looks like this

ID|Q1|Q2|Q3|Q4
---------------
11|A|A|C|B
13|C|C|C|A


Q1 to Q4 contains the answer of a multiple choice question

I need to a query that can return the average answer in the for below eg

ID|Average
----------
11|A
12|C


Can anyone help?

 

[gmmastros]

There are a couple challenges here, mostly brought upon by the database structure. You should really think seriously about normalizing it, because then, this type of query would be trivial. It's also made more difficult since you want to average letters instead of numbers.

If should structure the data as...

ID | QuizNumber | Grade
-----------------------
11 | 1 | A
11 | 2 | A
11 | 3 | C
11 | 4 | B
13 | 1 | C
13 | 2 | C
13 | 3 | C
13 | 4 | A


This structure is more flexible because you don't have to modify your table structure to accomodate a 5th question. In fact, you could have millions of questions and the database wouldn't care.

With that being said, perhaps this code will help you out.

Select *, 
       Char(Convert(int, (ASCII(Q1) + ASCII(Q2) + ASCII(Q3) + ASCII(Q4)) / 4.0)) As AverageGrade
From   Table

*** Note: This will completely fail if any of the questions are left blank.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Geoka]

Just have a try with this code

CREATE TABLE TEST (ID Int, Q1 Char(1), Q2 Char(1), Q3 Char(1), Q4 Char(1))
INSERT INTO TEST VALUES(11, 'A','A','C','B')
INSERT INTO TEST VALUES(12, 'C','C','C','A')

Select ID,Question,Count(Question) Cnt InTo #Tmp From 
(Select ID, Q1 Question From Test Union All
Select ID, Q2 Question From Test Test1 Union All
Select ID, Q3 Question From Test Test2 Union All
Select ID, Q4 Question From Test Test3) MyTab
Group By ID,question

select Tmp.ID, Tmp.Question from #Tmp Tmp
Inner Join
(Select Distinct ID, Max(Cnt) Cnt From #Tmp
	Group By ID) Tmp1 On Tmp.ID = Tmp1.ID and Tmp.Cnt = Tmp1.Cnt
Order By Tmp.ID

 

[vongrunt]

What is the average for AABB?

AB?

ASCII A plus half bit?

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[borgkling2003]

ASCII wont work

what happens if

A = 1
B = 2
C = 3
D = 4

and the result is 4,4,1,2?

the average is 2.75.

That wont be right. Cause the D or 4 is the major reaccuring one.

 

[vongrunt]

> I need to a query that can return the [!]average[/!] answer in the for below eg...

Sorry, GIGO

I'm asking again: what if two answers have the same count (A A B B)?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]