CODEWITHSUNDEEP
sql-server-2012
Vibrant Learner
Vibrant Learner

Reputation: 37

Computing multiple averages in a table with multiple different conditions

I'm trying to compute average for multiple columns in my table and I'm trying to impose rules to calculate this average based on a where clause. Since I do not have distinct values on most of these columns, I am unable to do a group by clause to get the correct results. A simple where clause with all the conditions does not compute the average correctly. Is there a workaround for this problem?

My table looks like

col1  col2  col3  col4
0     -3     0     109
3      2    -1     -108
-4    -1     3      3
2      0     4      2
2      1    107    -2

My intention here is to compute avg for each column based on a rule. For ex:avg(col1) should include values between 0 and 100, avg(col2) should include values between -1 to -100, avg(col3) should include values between 0 and 100, avg(col4) should include values between -1 to -100.

select avg(col1), avg(col2), avg(col3), avg(col4) from tbl1
where (col1 between 0 and 100) and (col1 between -1 and -100) and (col3 between 0 and 100) and (col4 between -1 and -100)

Upvotes: 1

Views: 525

Answers (1)

artemis
artemis

Reputation: 7241

The following solution should accomplish your objectives. First, we use some CTE (subqueries) to just limit the values in the columns to what we want, then we get their averages.

WITH
    first_col AS
        (
            SELECT col1
            FROM the_table
            WHERE col1 >= 0 and col1 <= 100
        ),

    second_col AS
        (
            SELECT col2
            FROM the_table
            WHERE col2 >= -100 and col2 <= -1
        ),

    third_col AS
        (
            SELECT col3
            FROM the_table
            WHERE col3 >= 0 and col3 <= 100
        ),

    fourth_col AS
        (
            SELECT col4
            FROM the_table
            WHERE col4 >= -100 and col4 <= -1
        )

    SELECT
        AVG(first_col.col1),
        AVG(second_col.col2),
        AVG(third_col.col3),
        AVG(fourth_col.col4)

    FROM
        first_col,
        second_col,
        third_col,
        fourth_col

You can look at this SQL FIDDLE for the result.

1   -2  2   -2

Alternatively, you can use this query to avoid using CTE

SELECT
AVG(CASE WHEN col1 >= 0 AND col1 <= 100 THEN col1 END) AS col1avg,
AVG(CASE WHEN col2 >= -100 AND col2 <= -1 THEN col2 END) AS col2avg,
AVG(CASE WHEN col3 >= 0 AND col3 <= 100 THEN col3 END) AS col3avg,
AVG(CASE WHEN col4 >= -100 AND col4 <= -1 THEN col4 END) AS col4avg
FROM
the_table

And you can see the results on this SQL FIDDLE

1   -2  2   -2
  • Note - This does assume you want the values inclusive, since the BETWEEN statement is inclusive.

Upvotes: 1

Related Questions