Solved

Average columns togather

Posted on 2014-03-31
104 Views
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
``````
0
Question by:RecipeDan
• 5
• 3
• 2
• +1

LVL 39

Expert Comment

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

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
``````
0

LVL 39

Accepted Solution

lcohan earned 500 total points
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

It is still adding the columns
0

LVL 39

Expert Comment

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

LVL 69

Expert Comment

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

The average of columns Jan_Total and Feb_Total
0

LVL 1

Author Comment

I am looking for something like this:

Avg(Jan_Total + Feb_Total) AS TwoMonthAvg
0

LVL 69

Expert Comment

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

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

It is better if you can provide some sample data and what exactly you want as result?
0

Featured Post

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.