May 14, 2012 at 12:12 am
Hi all,
I have stuck with some freaking problem....
Following table which i have to deal....
[Session_Type_ID] [Contribution][Learning] [Intellectual]
1 9 10 0
2 5 7 8
3 4 2 9
4 5 9 5
[Effectiveness] [Program_Name] [Session_Title] [Session_Taken_By]
5 KBC ASP.NET PRASHANT
7 KBC SQL TARUN
6 KBC VB.NET MAYUR
8 XYZ HTML RAJIV
I have to select Contribution, Learning, Intellectual, Effectiveness in such a way so that the rating of below 3 and above 10 will not be counted as well as the average of them counted in group of Session_Taken_By and Session_Title and for same program name
The Columns named Contribution,Learning,Intellectual,Effectiveness having rating received from Peoples and I required that rating will be average of that columns group by by session_taken_by and Session_title
please refered following table as I required
Faculty Topics Contribution learning Intellectual Effectiveness
PRASHANT ASP.NET 7.9 7.8 7.8 8.1
TARUN SQL 7.7 7.8 7.9 7.7
MAYUR VB.NET 7.9 7.9 7.9 7.8
RAJIV HTML 7.3 7.2 7.0 7.2
.
May 14, 2012 at 12:47 am
Faculty Topics Contribution learning Intellectual Effectiveness
PRASHANT ASP.NET 7.9 7.8 7.8 8.1
TARUN SQL 7.7 7.8 7.9 7.7
MAYUR VB.NET 7.9 7.9 7.9 7.8
RAJIV HTML 7.3 7.2 7.0 7.2
How are you getting all these values??....What's the logic behind it??...Show me one calculation of one of the columns.
May 14, 2012 at 6:29 am
You need to aggregate data, and then, once aggregated filter out the results? Is that a good summary? There are two approaches to this. First, use the HAVING clause as a mechanism for filtering aggregation data. You can then AVG or SUM your columns and use the HAVING clause to filter them.
The other approach is to use derived tables. You write a query that correctly aggregates your data and then you make that SELECT statement into a table:
SELECT *
FROM (SELECT AVG(MyValue) AS AvgMyValue
FROM MyTable
GROUP BY MyName) AS AggTable
WHERE AvgMyValue > 42;
Along those lines will also work. Try a couple of these and if you have problems, post the query that you've written and outline where it's gone wrong for you.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply