Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Would like to successfully filter data then calculate the average

Posted on 2010-09-03
11
424 Views
Last Modified: 2012-05-10
Hello,  I had a previous question whereas it was suggested to create a view to calculate and sort on the average.  I have a view with the calculated average, but to do an average, you need to do a group by.  I have created one view(MT_VIEW) which has all the the information I need from previous joins.  I am able to list the correct data based on what value has been given for the different parameters (date, title, segment, id_namecode, name, question1,....question30).
 I would like to be able to see the average for example, question5.  But, I am to build an an average column in the same MT_VIEW?  I know that after the filtering, I would want to list the name based on the average (highest to lowest).  Do I build a new view for averages only?  If I do that, I will need to group and I would need to make certain that for a specific date range, segment, etc that I get those averages only.  Any suggestions on what to do?  Maybe I missed the entire point of a View??
Thank you in advance.
0
Comment
Question by:mtrout
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 33602261
You say that the average is an average for one question (question 5 is your example) .

So I don't understand this bit..

'I would want to list the name based on the average (highest to lowest)'

How does one person have an 'average' for one question?  Surely one person has a Score for a quesstion.
0
 
LVL 20

Expert Comment

by:flow01
ID: 33603542
What is the definition of your MT_VIEW ?
What filtering do you want to do ?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 33604148
What is the previous question?

What data do you have that needs to be averaged?

mlmcc
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:mtrout
ID: 33614619
Peter57r:You say that the average is an average for one question (question 5 is your example) .

So I don't understand this bit..

'I would want to list the name based on the average (highest to lowest)'

How does one person have an 'average' for one question?  Surely one person has a Score for a quesstion.

Each person receives evaluations over a period of time.  The evaluations are collected and when we look at question 5 (how did this person do? for example), we take the average of question 5 (the ranking from 5 to 1) and then we list the persons based upon the collected evaluations looking at question 5 and listing them highest to lowest based on question 5.  
Example:
Jack                    5.00
Tony                   4.79
Tom                    4.67
Mac                    4.50
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 500 total points
ID: 33615762
So you are saying that for each question you want the people listed in descending order of the value in the record.
This would be theoretically possible in CR but having 30 questions to deal with makes it too laborious in my opinion.
So in terms of your original post, yes, you need another query to sort the data out.
The straightforward approach is to restructure the data using a union query in the database.
The structure of this query would be something like..
Select Studentid, Studentname, othercommonfields, "Q01" as Question, Q1AverageScore as Score from MT_view
Union
Select Studentid, Studentname, othercommonfields, "Q02" as Question, Q2AverageScore as Score from MT_view
 Union
.......repeated for every question
Union
 Select Studentid, Studentname, othercommonfields, "Q30" as Question, Q30AverageScore as Score from MT_view
 
Then you can base you report on this query and it will be quite straightforward to group results by Question and sort the students  by descending score.
 
 
0
 

Author Comment

by:mtrout
ID: 33618169
Peter57r, Thank you.  No, not for each question.  I would need to get an average for each question, but the persons would be grouped.  So, all evals for Jack are collected, I have calculated averages for all of the selected questions, I do the same for Tony; collect all the evals and I have calculated the averages for all fof all of the selected quesitons, etc.  So, I should have said for each group, where the person's name is a group because I am collection all evals for Jack, vs Tony, etc.  But I would arrange the list based on the final average for just the results for the calculated average for Question 5 for each person.
 I would guess that I would need to use Union.  I would need to read about that.  
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 33621778
You can try this

Open the report
Click REPORT --> GROUP SORT EXPERT
You can sort groups based on summary functions
  if the average of each question is a summary you could use it

mlmcc
0
 

Author Comment

by:mtrout
ID: 33621984
Thank you.  However,  This is where we are trying to get to.  We were here before, but unfortunately, found that the Average function in Crystal counts any zeros or nulls that may be given.  Since thse are evaluations, we don't wnat to count something that someone filled out a N/A=0 or left it blank=Null.  We have tried using the views, but need to be able to group after other criterias(filters) have been met.  Still investigating, maybe the union is the way to go?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 33622069
You will need to filter the records so the unwanted values are passed in.

mlmcc
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 500 total points
ID: 33625240
If you pass 'complete' records into CR then you would have to count non-zero values yourself in formula fields in order to calculate your own avarage.
'normalising' the data and using a Union query you can remove all the zero/null value entries and then let CR calculate the averages.
0
 

Author Closing Comment

by:mtrout
ID: 33627397
I still have a little difficulty understanding the union, but I have a databasae person that I can talk to that can show me.  Thank you again.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
clob to char in oracle 3 62
make null the repeated levels 2 31
Parameter Requester showing comma in number field 7 36
Error in creating a view. 8 22
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question