Search Posts

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

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

Source: Stackoverflow

Leave a Reply

Your email address will not be published. Required fields are marked *