• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Data retrieval

 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
;
 
Ranch Hand
Posts: 150
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 168
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
reply
    Bookmark Topic Watch Topic
  • New Topic