It's not a secret anymore!
The moose likes JDBC and Relational Databases and the fly likes Data retrieval Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Data retrieval" Watch "Data retrieval" New topic

Data retrieval

jyoti Agrawal

Joined: Nov 26, 2002
Posts: 3
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

Joined: Dec 12, 2001
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

Joined: Apr 23, 2002
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 ]
I agree. Here's the link:
subject: Data retrieval
It's not a secret anymore!