title: Dynamic group by in a MySql query
body: <p>I have an <code>employees</code> table which has fields like <code>Name, Salary, City, Country, Department</code>. I also have a user input where user can select multiple column headers e.g. City, Country etc. from my employees table. I need to make a dynamic query that groups-by all the fields the user has selected, and returns the result. E.g. Sum of all salaries by city, sum of all salaries by country, sum of all salaries by department by country etc.</p>
  <p>Here is what I am doing - ('$a' is the variable I get from the dropdown).</p>
  <pre><code>SELECT
       sum(salary) as sum_salary,
       IF(LOCATE('department', '$a$') &gt; 0, department, NULL)  as 'department',
       IF(LOCATE('city','$a$') &gt; 0, city , NULL)  as 'city'
       IF(LOCATE('country','$a$') &gt; 0, country , NULL)  as 'country'
  from tbl_employees
  GROUP BY
       IF(LOCATE('department', '$a$') &gt; 0, department, NULL)  as 'department',
       IF(LOCATE('city','$a$') &gt; 0, city , NULL)  as 'city'
       IF(LOCATE('country','$a$') &gt; 0, country , NULL)  as 'country'
  LIMIT 10000
  </code></pre>
  <p>SAMPLE DATA</p>
  <pre><code>NAME | SALARY | CITY | COUNTRY| DEPARTMENT
  n1     50       Chi    US       Design
  n2     100      Chi    US       Product
  n3     500      SF     US       Engg
  n5     20       SF     US       Engg
  n1     200      SF     US       Product
  </code></pre>
  <p>If '$a' = 'city', here is what I want</p>
  <pre><code>   SUM(SALARY) |   CITY
     150             Chi
     720              SF
  </code></pre>
  <p>Here is what I get</p>
  <pre><code>   SUM(SALARY) |   CITY  | COUNTRY  | DEPARTMENT
     150             Chi     NULL       NULL
     720              SF     NULL       NULL
  </code></pre>
  <p>The problem with this is if the user has selected only <code>city</code> as the group-by column, then I also see the <code>department</code> and <code>country</code> columns with all NULL values. How do I remove the completely NULL columns. Is this the right way to achieve this? And how can I remove the columns that completely have NULL values. Thanks!</p>
  <p>JS Fiddle - <a href="https://www.db-fiddle.com/f/cy7swwpphFSNssHPpZgKtH/1" rel="nofollow noreferrer">https://www.db-fiddle.com/f/cy7swwpphFSNssHPpZgKtH/1</a></p>