I have the following table 'Tasks'

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

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

CodePudding user response：

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

CodePudding user response：

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).

CodePudding user response：

-- 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

CodePudding user response：

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