Average columns togather

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
``````
LVL 1
Who is Participating?

Database AnalystCommented:
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

Database AnalystCommented:
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

Author Commented:
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

Author Commented:
It is still adding the columns
0

Database AnalystCommented:
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

Senior DBACommented:
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

Author Commented:
The average of columns Jan_Total and Feb_Total
0

Author Commented:
I am looking for something like this:

Avg(Jan_Total + Feb_Total) AS TwoMonthAvg
0

Senior DBACommented:
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

Author Commented:
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

Data EngineerCommented:
It is better if you can provide some sample data and what exactly you want as result?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.