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.
I want a table like with two columns per grouping column like this.
This is more simply done in separate rows with
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.