How to group a table in Sql?

Question

I have the following table, Tasks:

ID	Task	Type	Time_Taken
1	task1	type1	01h:00m
2	task1	type2	02h:00m
3	task2	type1	00h:30m
4	task2	type2	00h:30m
5	task3	type1	01h:00m
6	task3	type2	04h:00m

I want to create a new table from the Tasks table which should be

Task	type1_time	type2_time
task1	01h:00m	02h:00m
task2	00h:30m	00h:30m
task3	01h:00m	04h:00m

Can you help me in building an sql query for this?

Answer 1

My query will you give you the result as your are expecting. Demo link: db-fiddle

SELECT Task, 
SEC_TO_TIME(sum(if(Type='type1', TIME_TO_SEC(Time_Taken),0))) as type1_time,
SEC_TO_TIME(sum(if(Type='type2', TIME_TO_SEC(Time_Taken),0))) as type2_time 
FROM Tasks 
GROUP BY Task

Answer 2

You have one entry per task and type. As you want to group by task, you can use MIN or MAX to access the type's value, as there is only one.

select
  task,
  min(case when type = 'type1' then time_taken end) as type1time,
  min(case when type = 'type2' then time_taken end) as type2time
from tasks
group by task
order by task;

This is called conditional aggregation, because we use an aggregate function on a condition (here: when the row matches the desired type).

Answer 3

-- Solution INNER JOIN
SELECT type1.Task, type1.Time_Taken as 'type1_time', type2.Time_Taken as 'type2_time'
FROM Task type1 
INNER JOIN Task type2 on type1.Task = type2.Task
WHERE type1.Type = 'type1'
    AND type2.Type = 'type2';

--Solution GROUP_CONCAT
SELECT  
        t.Task,
        GROUP_CONCAT(IF(t.Type = "type1", t.Time_Taken, NULL)) AS 'type1_time',
        GROUP_CONCAT(IF(t.Type = "type2", t.Time_Taken, NULL)) AS 'type2_time'
FROM Task t
GROUP BY t.Task;

DEMO : https://paiza.io/projects/e/V9GtkWoJQAqewynjRXZ9-Q?theme=twilight

Answer 4

I assume this SQL query is processed in MySQL database. Please refer to the query below:

select Task, 
sum(if(Type='type1', Time_Taken,0)) as type1_time,
sum(if(Type='type2', Time_Taken,0)) as type2_time from Tasks 
group by Task