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.

Answer

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.