AVG excluding Null - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

AVG excluding Null

Author

Topic

d0o0n7
Starting Member

3 Posts

Posted - 2011-04-20 : 17:44:00

Can someone PLEASE help me with a query.. Its simple though tough... :( I have 4 columns and I need an avg based on date criteria or ID.. I have some Null values in between so when I am using AVG its showing Null for entire Column.. when I am using isnull its dragging down the average because its including 0 as well.. I tried Case when and it worked fine in SQL Server however I am creating this Query for Excel VBA ADO.. and there the query goes too long there.. the query that I had earlier (AVG one) worked fine.. I am looking out for any quick fix to exclude NULL values from table .. Thanks :) hope you understand!!

Here is my query

Select A_Date, EmpID, Emp_Name, 
AVG((
[Flexibility_shown_towards_Teams_Target] + 
[Plans_avails_leave_as_per_team_quota] + 
[Compliance_to_Productive_Hours] + 
[Flexibility_to_change_in_Work]))/4 as People_Teamwork, 
AVG(([Follow_FIFO_methodology] + 
[Following_SOT_to_PnP] + 
[Confirm_the_completion_of_all_case]))/3 as Judgment_Decision_Making, 
AVG(([1_Bright_idea_Approved] + [1_Bright_Idea_implemented]))/2 as Change_innovation, 
AVG(([Compliance_with_Activity_Tracker] + [Compliance_to_Transport] + 
[PKT_Scores]))/3 as Demonstrate_Accountability_and_High_Performance, 
AVG(([Participation_membership_in_any_CSR_activity] + 
[Participation_membership_in_Company_group_or_activity]))/2 as Corporate_Social_Responsibilities, 
AVG(([Identifying_a_Mentee] + [Working_as_a_Team] + 
[Maintaining_consistency_in_self_perf_showing_impr_in_Mentees_res]))/3 as Mentor_Mentee
From My_Data
where A_date BETWEEN '04/01/11' AND '04/01/11' and EmpID = '000672516' 
group by A_Date, EmpID, Emp_Name

quote:
I am Only looking out for SQL Query can be excuted in EXCEL VBA

Dev

if everyone is thinking in a same way then no one is thinking...

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-20 : 18:08:25

You could do something like this - I am showing only an example


SUM(ISNULL(col1,0.)+ISNULL(col2,0.)+ISNULL(col3,0.)+ISNULL(col4,0.))
/
(COUNT(col1)+COUNT(col2)+COUNT(col3)+COUNT(col4))

d0o0n7
Starting Member

3 Posts

Posted - 2011-04-21 : 07:48:50

I tried this in SQL it worked but I am not sure if it would work in VBA.. I will try and will post by Tonight..

Also I am having problem in group by

I will explain Once I will try this code over VBA ADO

Thanks for the response.

Dev

if everyone is thinking in a same way then no one is thinking...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-21 : 07:51:33

SQL Server only processes Transact-SQL, not VBA. Excel can interact with SQL Server using VBA, but the commands it sends to SQL Server must be T-SQL.

d0o0n7
Starting Member

3 Posts

Posted - 2011-04-21 : 12:47:37

Yes, I am just using SQL Query however the table that I have is in Excel and I am running vba code using Recordsets. but query seems too large to process. .however AVG function was working fine but result is not accurate as mentioned in my earlier mail...

Thanks for the reply.

quote:
Originally posted by robvolk

SQL Server only processes Transact-SQL, not VBA. Excel can interact with SQL Server using VBA, but the commands it sends to SQL Server must be T-SQL.

Dev

if everyone is thinking in a same way then no one is thinking...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources