I need to divide time from table A by table B to find relative completion time in % of a task. The time column shows the time student needed to finish a task.
if the total time it took was 2 min, and the attempt time was 1 min, then the relative time is 50%
One table has 30 rows - it' s a derived table that shows total time,
another 59 rows - time it took for each step without summation,
common field is named st_id (step_id).
The first table A :
+-------+---------+
| st_id | time |
+-------+---------+
| 10 | 0:00:28 |
| 11 | 0:01:51 |
| 12 | 0:10:09 |
| 13 | 0:03:44 |
| 14 | 0:16:44 |
| 15 | 0:19:12 |
| 16 | 0:03:43 |
| 17 | 0:07:56 |
| 18 | 0:06:19 |
| 19 | 0:31:14 |
| 20 | 0:11:50 |
| 81 | 0:14:12 |
| 82 | 0:18:59 |
| 83 | 0:30:24 |
| 84 | 0:11:50 |
| 85 | 0:40:35 |
| 86 | 1:28:57 |
| 87 | 0:24:10 |
| 88 | 0:51:53 |
| 100 | 0:05:31 |
| 101 | 0:13:41 |
| 103 | 0:11:50 |
| 104 | 0:22:18 |
| 105 | 0:35:18 |
| 106 | 0:55:02 |
| 107 | 0:42:02 |
| 108 | 0:08:52 |
| 109 | 1:07:07 |
| 110 | 0:11:50 |
| 111 | 0:11:55 |
+-------+---------+
another table has 59 rows - table B:
+------------+---------+---------------+
| student_id | st_id | time |
+------------+---------+---------------+
| 59 | 10 | 0:00:28 |
| 59 | 11 | 0:01:11 |
| 59 | 12 | 0:06:09 |
| 59 | 13 | 0:02:24 |
| 59 | 14 | 0:00:05 |
| 59 | 14 | 0:00:04 |
| 59 | 14 | 0:00:20 |
| 59 | 14 | 0:00:23 |
| 59 | 14 | 0:09:42 |
| 59 | 14 | 0:00:10 |
| 59 | 15 | 0:11:44 |
| 59 | 15 | 0:00:08 |
| 59 | 16 | 0:02:23 |
| 59 | 17 | 0:04:53 |
| 59 | 17 | 0:00:23 |
| 59 | 18 | 0:00:07 |
| 59 | 18 | 0:03:19 |
| 59 | 18 | 0:00:53 |
| 59 | 19 | 0:00:07 |
| 59 | 19 | 0:00:29 |
| 59 | 19 | 0:00:10 |
| 59 | 19 | 0:00:06 |
| 59 | 19 | 0:00:04 |
| 59 | 19 | 0:00:07 |
| 59 | 19 | 0:00:24 |
| 59 | 19 | 0:00:08 |
| 59 | 19 | 0:00:08 |
| 59 | 19 | 0:02:11 |
| 59 | 19 | 0:00:10 |
| 59 | 19 | 0:15:50 |
| 59 | 20 | 0:07:10 |
| 59 | 81 | 0:08:52 |
| 59 | 82 | 0:11:31 |
| 59 | 82 | 0:00:08 |
| 59 | 83 | 0:00:04 |
| 59 | 83 | 0:00:08 |
| 59 | 83 | 0:00:51 |
| 59 | 83 | 0:00:15 |
| 59 | 83 | 0:17:46 |
| 59 | 84 | 0:07:10 |
| 59 | 85 | 0:24:35 |
| 59 | 86 | 0:53:14 |
| 59 | 86 | 0:00:23 |
| 59 | 87 | 0:07:10 |
| 59 | 87 | 0:07:40 |
| 59 | 88 | 0:31:13 |
| 59 | 100 | 0:03:31 |
| 59 | 101 | 0:08:21 |
| 59 | 103 | 0:07:10 |
| 59 | 104 | 0:13:38 |
| 59 | 105 | 0:21:18 |
| 59 | 106 | 0:33:02 |
| 59 | 107 | 0:07:10 |
| 59 | 107 | 0:18:12 |
| 59 | 108 | 0:05:32 |
| 59 | 109 | 0:40:27 |
| 59 | 110 | 0:07:10 |
| 59 | 111 | 0:07:15 |
+------------+---------+---------------+
I tried to inner join by step_id, but it gives me the error: "derived table has diff num of rows"
But i m only trying to join those that exist in both tables? that's how inner join works?
I have both tables inside with clause, they pull attempt time data for student_id = 59:
with step_helper(st_id, t) as (
select step_id, sec_to_time(sum(t)) as total_time from (select step_id,
case when (submission_time - attempt_time) > 3600 then sec_to_time((select * from s1))
else sec_to_time((submission_time - attempt_time)) end as t
from step_student where student_id = 59)k
group by step_id),
time_attempt(st_id, t) as (
select student.student_id, step.step_id,
case when (submission_time - attempt_time) > 3600 then sec_to_time((select * from s1))
else sec_to_time((submission_time - attempt_time)) end as Время_попытки
from step_student inner join student on student.student_id = step_student.student_id
inner join step on step.step_id = step_student.step_id where student.student_id = 59)
this inner joining the 2 tables above does not work:
SELECT *FROM time_attempt INNER JOIN step_helper ON time_attempt.st_id = step_helper.st_id
In definition of view, derived table or common table expression, SELECT list and column names list have different column counts
The problem is here time_attempt(st_id, t) as (. The columns in SELECT sub-query for time_attempt are 3 however you only list 2. As per MySQL documentation
The number of names in the list must be the same as the number of columns in the result set.
Therefore, your option is either listing all three columns from the result set time_attempt(st_id, t, ttt) as ( or don't list them at all time_attempt as (.
Update:
Here is an update fiddle to prove a point that it's not about row differences that return the error.