CODEWITHSUNDEEP
sqloracle-database
Sandipan Bhattacharyya
Sandipan Bhattacharyya

Reputation: 137

Group by average of column by value of another column present and not present

Let's say my dummy data looks as follows.

Col1          Col2
A              10
A              20
B              30
C              10
C              50
C              60

I want to get a table which looks as follows.

Distinct Col1       Avg       Not_Avg
A                    15          37.5
B                    30          30
C                    40          20

So basically avg when a value is present vs when it is not. I am working on Oracle SQL. Can anyone please guide me on how to do this?

Upvotes: 0

Views: 1230

Answers (2)

Frank Ockenfuss
Frank Ockenfuss

Reputation: 2043

Try this 

    -- test data
    with data(Col1,
    Col2) as
     (select 'A', 10
        from dual
      union all
      select 'A', 20
        from dual
      union all
      select 'B', 30
        from dual
      union all
      select 'C', 10
        from dual
      union all
      select 'C', 50
        from dual
      union all
      select 'C', 60
        from dual)
    -- select avg and avg of not equal col1
    select d1.col1, avg(d1.col2), (select avg(col2) from data d2 where d2.col1<> d1.col1)
    from data d1
    group by d1.col1

Upvotes: 0

Vladimir Baranov
Vladimir Baranov

Reputation: 32695

One variant is to GROUP the data twice.

At first GROUP BY Col1 to calculate simple average.

Second time join this intermediate result to the original table to get rows with "other" values and calculate average again.

WITH
CTE_Avg
AS
(
    SELECT
        Col1
        ,AVG(Col2) AS AvgCol2
    FROM T
    GROUP BY Col1
)
SELECT
    CTE_Avg.Col1
    ,CTE_Avg.AvgCol2
    ,AVG(T.Col2) AS Not_Avg
FROM
    CTE_Avg
    INNER JOIN T ON T.Col1 <> CTE_Avg.Col1
GROUP BY
    CTE_Avg.Col1
    ,CTE_Avg.AvgCol2
;

Upvotes: 1

Related Questions