title: Group by Rollup with 3+ columns
body: <p>This post is a reference to the question I posted here: </p>
  
  <p><a href="https://stackoverflow.com/questions/54884165/sql-group-by-rollup-with-multiple-columns/54884964#54884964">SQL: Group by Rollup with multiple columns</a></p>
  
  <p>I wasn't specific enough that the query needed to work for 3-5 columns (rather than just 2 in the simplified example), so I am trying again with an expanded example:</p>
  
  <pre><code>create table income_region (year int,region varchar(40),income float, sex varchar(10))
  
  insert into income_region (income,region,year,sex) values (2000,'North America', 2000,'Male')
  insert into income_region (income,region,year,sex) values(2200,'Europe', 2000,'Male')
  insert into income_region (income,region,year,sex) values (2000,'North America', 2000,'Female')
  insert into income_region (income,region,year,sex) values(2200,'Europe', 2000,'Female')
  insert into income_region (income,region,year,sex) values(2101,'North America', 2001,'Male')
  insert into income_region (income,region,year,sex) values(2001,'Europe', 2001,'Male')
  insert into income_region (income,region,year,sex) values(2101,'North America', 2001,'Female')
  insert into income_region (income,region,year,sex) values(2001,'Europe', 2001,'Female')
  </code></pre>
  
  <p>Below is one of the few queries I tried (based modifications from the answer I got in the linked post) which does not give the desired output:</p>
  
  <pre><code>select avg(income) as avg_income ,region,year as year,sex as sex
  from income_region group by region,year,sex with rollup
  Union all 
  Select avg(income) as avg_income ,null as region,year as year, null as sex
  from income_region group by year,sex
  </code></pre>
  
  <p>What the above query is lacking is null values for the "year" column. More specifically, these are the extra rows I want inserted as well. </p>
  
  <pre><code>Income    Region        Year       Sex
  
  2300      Europe         Null      Female
  2101      Europe         Null      Male
  2100.5    North America  Null      Male
  2400      North America  Null      Female
  </code></pre>