Hello Friends,
I am having a general query problem. I am having a table with data as below.
SRNO EMP_ID DATETIME QUES1 QUES2 QUES3 QUES4 QUES5 QUES6 QUES7 QUES8
1 10 11111 2 4 4 NA 2 2
NA 5
2 12 11111 NA 4 5 4 3 NA
3 4
3 10 11111 3 3 NA 4 5 NA
5 3
4 14 11111 5 NA 3 2 3 5
3 3
5 10 11111 2 4 2 5 NA 3
5 2
6 15 11111 5 2 3 NA 3 5
3 4
7 12 11111 3 4 5 3 4 2
NA 4
I now want to show a report from this table.This report will be group by
EMP_ID and average of QUES1,QUES2 etc.
so the report will look like
EMP_ID SURVEYS QUES1 QUES2 QUES3 QUES4 QUES5 QUES6 QUES7
QUES8
10 3 2.3 3.6 3 4.5 3.5 2.5
5 3.33
12 2
14 1
15 1 5 2 3 NA 3 5
3 4
Some values are not shown in above report . But this will be shown from the
result of the query.
So the main problem is for calculating average of QUES3 for EMP_ID=10.
This average is calculating without considering NA records. So for QUES3
answers are 4,NA,2 so the average will be 6/2 and not 6/3.
Because we do not want to consider the records with NA as answer in average
calculations.
So from normal queries if we do
SELECT AVG(QUES1),AVG(QUES2),..................... FROM TABLE GROUP BY
EMP_ID
this does not gives correct answer.
Can anyone suggest a solution for the above addressed problems.
If you want any further information , your enquiries are welcome.
Thanks,
Pravin Nirmal
---------------------------------------------------------------------
Pravin Nirmal
Team Leader
Email: [EMAIL PROTECTED]
Mail: A Wing, 3rd Floor, Mittal Court, off Ambedkar Road,
Rasta Peth, Pune 411001, INDIA.
Web: http://www.clarionsys.com
---------------------------------------------------------------------
Clarion Technologies Pvt. Ltd.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
