Win a copy of The Way of the Web Tester: A Beginner's Guide to Automating Tests this week in the Testing forum!

# Data retrieval

jyoti Agrawal
Greenhorn
Posts: 3
Hi,
How to retrive data from one table having value A,B,C (grade) and calculate average for different column using mysql.( I believe mysql does not support decode function)I am using this query but it makes my program length since I need to do it for every column as well for "B","C" etc.(A=4,B=3 ...)
select paperID, Count(*) * 4
from Review_JA
where overallrecommendation ="A"
group by paperID
;

Tina Coleman
Ranch Hand
Posts: 150
Try coming up with a more general solution than running a separate query for each grade. It looks like you're trying to get an average grade per paper, that each paper may have multiple records in your table (hence the group by). I suspect you may end up wanting a generic way to figure out the points earned per grade, and then writing a query that adds up the total points received for the paper (by calculating the grade times the points for the grade), and then dividing by the number of grades for the paper.
This looks vaguely like a homework problem (and it is exam/project time for US colleges, at least), so I'm loathe to give any more specifics. But that should be more than enough to get you started.

Michael Zalewski
Ranch Hand
Posts: 168
The way to do this depends on the database.
The basic idea is to first write a function that will transform "A", "B", "C" ... into 4, 3, 2. Then use SUM() to add the value of that function instead of COUNT(*).
For example, in Oracle you should be able to do

In Microsoft Access, you can probably do

(but this only works if overallrecommendation is never null and never anything other than A, B, C, D, E. Might be better to use Access IIF).
[ November 26, 2002: Message edited by: Michael Zalewski ]