asked on

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

Open in new window

lcohan

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

RecipeDan

ASKER

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

ASKER CERTIFIED SOLUTION

lcohan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RecipeDan

ASKER

It is still adding the columns

lcohan

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;

Scott Pletcher

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?

RecipeDan

ASKER

The average of columns Jan_Total and Feb_Total

RecipeDan

ASKER

I am looking for something like this:

Avg(Jan_Total + Feb_Total) AS TwoMonthAvg

Scott Pletcher

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?

RecipeDan

ASKER

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)

Sharath S

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