Solved

Average columns togather

Posted on 2014-03-31
11
104 Views
Last Modified: 2014-08-23
Hello:

I am trying to average two columns together. When I average them it gets adds the columns together instead of averaging it.

SELECT Question, JAN_Y, JAN_N, JAN_NA, JAN_TOTAL, FEB_Y, FEB_N, FEB_NA, FEB_TOTAL, AVG(JAN_TOTAL + FEB_TOTAL) AS TOTAL
(
SELECT Question, JAN_Y, JAN_N, JAN_NA, CONVERT(numeric(5,2), JAN_TOTAL) AS JAN_TOTAL, FEB_Y, FEB_N, FEB_NA, CONVERT(numeric(5,2), FEB_TOTAL) AS FEB_TOTAL   
FROM
(
SELECT Question,
JAN_Y,
JAN_N,
JAN_NA,
ISNULL((JAN_Y)/NULLIF((JAN_Y+JAN_N),0),0)*100 AS JAN_TOTAL,
FEB_Y,
FEB_N,
FEB_NA,
ISNULL((FEB_Y)/NULLIF((FEB_Y+FEB_N),0),0)*100 AS FEB_TOTAL
FROM
DATA
)
TABLE1
)
TABLE2
GROUP BY Question, JAN_Y, JAN_N, JAN_NA, JAN_TOTAL, FEB_Y, FEB_N, FEB_NA, FEB_TOTAL 

Open in new window

0
Comment
Question by:RecipeDan
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
I'm not 100% sure what do you mean however...why not using SQL Aerage function instead?

http://technet.microsoft.com/en-us/library/ms177677(v=sql.105).aspx
0
 
LVL 1

Author Comment

by:RecipeDan
Comment Utility
I am using the average function

SELECT Question, JAN_Y, JAN_N, JAN_NA, JAN_TOTAL, FEB_Y, FEB_N, FEB_NA, FEB_TOTAL, AVG(JAN_TOTAL + FEB_TOTAL) AS TOTAL

Open in new window

0
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
Comment Utility
I meant like:

SELECT Question,
      JAN_Y,
      JAN_N,
      JAN_NA,
      CONVERT(numeric(9,2), ISNULL((JAN_Y)/NULLIF((JAN_Y+JAN_N),0),0)*100) AS JAN_TOTAL,
      FEB_Y,
      FEB_N,
      FEB_NA,
      CONVERT(numeric(9,2), ISNULL((FEB_Y)/NULLIF((FEB_Y+FEB_N),0),0)*100) AS FEB_TOTAL,
      AVG(JAN_TOTAL + FEB_TOTAL)
FROM DATA
GROUP BY Question, JAN_Y, JAN_N, JAN_NA, JAN_TOTAL, FEB_Y, FEB_N, FEB_NA, FEB_TOTAL
0
 
LVL 1

Author Comment

by:RecipeDan
Comment Utility
It is still adding the columns
0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
create view test as
SELECT Question,
      JAN_Y,
      JAN_N,
      JAN_NA,
      CONVERT(numeric(9,2),ISNULL(JAN_Y,0))/CONVERT(numeric(9,2), NULLIF((JAN_Y+JAN_N),0),0) * 100 AS JAN_TOTAL,
      FEB_Y,
      FEB_N,
      FEB_NA,
      CONVERT(numeric(9,2),ISNULL(FEB_Y,0))/CONVERT(numeric(9,2), NULLIF((FEB_Y+FEB_N),0),0) * 100 AS FEB_TOTAL
FROM DATA WITH (NOLOCK);
GO

select Question, AVG(JAN_TOTAL + FEB_TOTAL) as AvgTotal
from test
GROUP BY Question;
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
This computation:
ISNULL((JAN_Y)/NULLIF((JAN_Y+JAN_N),0),0)*100 AS JAN_TOTAL
gives you a ratio (%) of Y to N: you can't accurately avg ratios anyway.

I think for this you need to use the other method of calculating an average: SUM() / COUNT().

That way, you can use CASE within the SUM and COUNT to get the specific average(s) you need.


I'd code it real quick for you, but I'm not sure what average(s) you're trying to get.

What average(s) are you trying to calculate?
0
 
LVL 1

Author Comment

by:RecipeDan
Comment Utility
The average of columns Jan_Total and Feb_Total
0
 
LVL 1

Author Comment

by:RecipeDan
Comment Utility
I am looking for something like this:

Avg(Jan_Total + Feb_Total) AS TwoMonthAvg
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
But "Jan_Total" and "Feb_Total" are themselves ratios, and you can't avg a ratio.

So do you want the average of the total of Jan_Y = 1 or Feb_Y = 1?  Or Jan and Feb?

That is, if the whole table had just one row, with Jan_Y = 1 and Feb_Y = 0, would the "avg" be 1.0 or 0.5?
0
 
LVL 1

Author Comment

by:RecipeDan
Comment Utility
For example say the Jan_Total is 89.75 and Feb_Total is 79.4. I want a column that shows 84.57. When finished I need the columns to look like this:

Jan_Y            Jan_N         Jan_NA     Jan_Total            Feb_Y           Feb_N          Feb_NA  Feb_Total             2MonthAvg (which is the averaging of Jan_total _ Feb_Total)
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
It is better if you can provide some sample data and what exactly you want as result?
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

772 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now