# SQL AVG applied to more than one by group?

I am trying to calculate a column average by group for several groups in the same query.

Assume there are 2 columns (a,b), for each I want the average of another column (c) per group of the first column. For example, this would return the a groups and average c value per a group.

```SELECT a, AVG(c)
FROM mytable
GROUP BY a
```

This gives me two columns.

a AVG(c)
1 0.5
2 0.75

I want a table like with two columns per grouping column like this.

a AVG(c) b AVG(c)
1 0.5 8 1.5
2 0.75 9 0.25

Thanks.

This is more simply done in separate rows with `union all`:

```SELECT 'a' as which, a, AVG(c)
FROM mytable
GROUP BY a
UNION ALL
SELECT 'b' as which, b, AVG(c)
FROM mytable
GROUP BY b;
```

If you really want them side-by-side, the query is quite a bit more complicated:

```SELECT MAX(a) as a, MAX(a_avg_c) as a_avg_c,
MAX(b) as b, MAX(b_avg_c) as b_avg_c
FROM ((SELECT a, AVG(c) as a_avg_c, null as b, null as b_avg_c,
ROW_NUMBER() OVER (ORDER BY a) as seqnum
FROM mytable
GROUP BY a
) UNION ALL
(SELECT null, null, b, AVG(c) as b_avc_c,
ROW_NUMBER() OVER (ORDER BY b) as seqnum
FROM mytable
GROUP BY b
)
) ab
GROUP BY seqnum;
```

This is more complicated because SQL treats a row as a single entity. You actually want columns on each row that are entirely unrelated to each other. So, this version creates a “relation” by assigning a sequential value and then aggregating by that value to get what you want.

User contributions licensed under: CC BY-SA
10 People found this is helpful