Fetch count of values as different columns for different values in MySql

Question

I have data in a MySQL table in the following format. I want to fetch count of string values as different column in MySql query, which means if column has 3 distinct values then 3 columns will be added to result while fetching the data from database.

Tables :

task table

id  | task      |
----|-----------|
1   | TASK1     |
----|-----------|
2   | TASK2     |
----|-----------|
3   | TASK3     |
----|-----------|

priority table

id  |  task_id  | priority  |
----|-----------|-----------|
1   |      1    |    Low    |
----|-----------|-----------|
2   |      1    |  Medium   |
----|-----------|-----------|
3   |      1    |   High    |
----|-----------|-----------|
4   |      1    |   High    |
----|-----------|-----------|
5   |      1    |   Low     |
----|-----------|-----------|
6   |      1    |   Medium  |
----|-----------|-----------|
7   |      1    |   High    |
 . 
 .
 N

Expected Result :

task |low_priority  | medium_priority | high_priority
-----|--------------|-----------------|--------------
TASK1|    2         |        2        |      3    
-----|--------------|-----------------|--------------

Now, I am trying with following query, but did't find expected result.

SELECT task, (CASE WHEN priority = 'low' THEN 1 ELSe 0 END ) AS 'low_priority',(CASE WHEN priority = 'medium' THEN 1 ELSE 0 END) AS 'medium_priority',(CASE WHEN priority = 'high' THEN 1 ELSE 0 END ) AS 'high_priority' FROM task LEFT JOIN priority ON priority.task_id = task.id GROUP BY task.id

Sinisa Bobic · Accepted Answer · 2018-02-12 12:11:53Z

you can do it on this way

SELECT task, SUM(CASE WHEN priority = 'Low' THEN 1 ELSE 0 END )   AS 'low_priority',
             SUM(CASE WHEN priority = 'Medium' THEN 1 ELSE 0 END) AS 'medium_priority',
             SUM(CASE WHEN priority = 'High' THEN 1 ELSE 0 END )  AS 'high_priority'
FROM task 
LEFT JOIN priority ON priority.task_id = task.id GROUP BY task.id
HAVING SUM(CASE WHEN priority = 'Low' THEN 1 ELSE 0 END ) > 0    OR 
       SUM(CASE WHEN priority = 'Medium' THEN 1 ELSE 0 END ) > 0 OR
       SUM(CASE WHEN priority = 'High' THEN 1 ELSE 0 END )

and result will be

+-------+--------------+-----------------+---------------+
| task  | low_priority | medium_priority | high_priority |
+-------+--------------+-----------------+---------------+
| TASK1 |            2 |               2 |             3 |
+-------+--------------+-----------------+---------------+
1 row in set (0.00 sec)

Collectives™ on Stack Overflow

Fetch count of values as different columns for different values in MySql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related