How to use the dynamically generated column in the same query without using join or cte

Question

I'm learning SQL. I've a situation where I've to use the same column which I created in runtime. The table is patient with 2 columns only which are dateOfCheckup and duration in minutes.

+---------------+-----------------+
| dateOfCheckup |   duration      | 
+---------------+-----------------+
|   2020-05-28  |  30 min         | 
|   2020-05-29  |  30 min         |
|   2020-05-30  |  1 hour         | 
|   2020-06-03  |  1 hour 30 min  | 
|   2020-06-05  |  30 min         |
|   2020-07-21  |  1 hour         |
|   2020-07-22  |  1 hour 30 min  | 
|   2020-07-28  |  1 hour 30 min  | 
+---------------+-----------------+

Now I'll create 1 more column in run time i.e. minutes (which is just duration converted to total minutes as integer value) with this query:

select dateOfCheckup, duration,
((case when duration like '% hour%' then substring_index(duration, ' hour', 1) * 60 else 0 end) + (case when duration like '%min%' then substring_index(substring_index(duration, ' min', 1), ' ', -1) + 0 else 0 end)) as minutes from patient;

This query is working perfectly. See the result:

+---------------+-----------------+-----------------+
| dateOfCheckup |  duration       |   minutes       |
+---------------+-----------------+-----------------+
|   2020-05-28  |  30 min         |   30            | 
|   2020-05-29  |  30 min         |   30            |
|   2020-05-30  |  1 hour         |   60            |
|   2020-06-03  |  1 hour 30 min  |   90            |
|   2020-06-05  |  30 min         |   30            |
|   2020-07-21  |  1 hour         |   60            |
|   2020-07-22  |  1 hour 30 min  |   90            |
|   2020-07-28  |  1 hour 30 min  |   90            |
+---------------+-----------------+-----------------+

My question is, What if I want to use this newly created column minutes in the same query for other tasks such as group by, order by, sum, avg, etc. I'm just asking if there's a scenario like this, then how do we achieve this. I tried using sum(minutes). The error is:

Unknown column 'minutes' in 'field list'

Here is a db<>fiddle. Please help me.

Answer 1

You have to treat current query as a sub query and then sum the result

SELECT SUM(minutes) AS total_time 
FROM (
    select dateOfCheckup, duration, 
    ((case when duration like '% hour%' then substring_index(duration, ' hour', 1) * 60 else 0 end) + 
    (case when duration like '%min%' then substring_index(substring_index(duration, ' min', 1), ' ', -1) + 0 else 0 end)
    ) as minutes 
from patient
) AS a;