Dynamic group by in a MySql query

Question

I have an employees table which has fields like Name, Salary, City, Country, Department. 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.

Here is what I am doing - ('$a' is the variable I get from the dropdown).

SELECT
     sum(salary) as sum_salary,
     IF(LOCATE('department', '$a$') > 0, department, NULL)  as 'department',
     IF(LOCATE('city','$a$') > 0, city , NULL)  as 'city'
     IF(LOCATE('country','$a$') > 0, country , NULL)  as 'country'
from tbl_employees
GROUP BY
     IF(LOCATE('department', '$a$') > 0, department, NULL)  as 'department',
     IF(LOCATE('city','$a$') > 0, city , NULL)  as 'city'
     IF(LOCATE('country','$a$') > 0, country , NULL)  as 'country'
LIMIT 10000

SAMPLE DATA

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

If '$a' = 'city', here is what I want

   SUM(SALARY) |   CITY
   150             Chi
   720              SF

Here is what I get

   SUM(SALARY) |   CITY  | COUNTRY  | DEPARTMENT
   150             Chi     NULL       NULL
   720              SF     NULL       NULL

The problem with this is if the user has selected only city as the group-by column, then I also see the department and country 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!

JS Fiddle - https://www.db-fiddle.com/f/cy7swwpphFSNssHPpZgKtH/1

See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — Strawberry, Feb 26 '21 at 17:57
@Strawberry - thanks, here is the fiddle - all I want to achieve is dynamic group by based on a string - https://www.db-fiddle.com/f/cy7swwpphFSNssHPpZgKtH/1 — Ujjwal Vaish, Feb 26 '21 at 18:48
My bad, I thought it was auto save, please try the new link, — Ujjwal Vaish, Feb 26 '21 at 18:52

score 0 · Answer 1 · answered Feb 26 '21 at 16:05

0

Use a Case statement to select the required field. then use a reference to the first column, 1 in group by

 SELECT
         case when LOCATE('department', '$a$') > 0 then department
              when LOCATE('city','$a$') > 0 then city
              when LOCATE('country','$a$') > 0 then country
         else Null
         end as field,
         sum(salary) as sum_salary,
    from tbl_employees
    GROUP BY 1    
    LIMIT 10000

answered Feb 26 '21 at 16:05

Syed

144
9

Thanks! But what if user has selected multiple group bys - e.g. department and country? – Ujjwal Vaish Feb 26 '21 at 16:08
i assumed only one field was required in the output before expected output was added to the question. I will update the answer if i can figure something for output with multiple fields – Syed Feb 26 '21 at 16:38
Note that LIMIT without ORDER BY is fairly meaningless – Strawberry Feb 26 '21 at 17:57

Dynamic group by in a MySql query

1 Answers1