Based on the provided code, it seems like you’re trying to summarize the data in a way that shows the total time spent on each reason and status. Here’s an updated SQL query that should achieve what you’re looking for:

SELECT 
    reason,
    status,
    SUM(minutes) AS total_minutes
FROM 
    (SELECT 
         shiftindex, reason, status, EXTRACT(EPOCH FROM duration) / 60 AS minutes
     FROM 
         your_table_name)
GROUP BY 
    reason, status
ORDER BY 
    total_minutes DESC;

In this query:

• We first calculate the duration in minutes for each row using EXTRACT(EPOCH FROM duration) / 60.
• Then we group by reason and status, and sum up the minutes column to get the total time spent on each combination.
• Finally, we order the results by the total_minutes column in descending order.

This should give you a summary of the data that shows the total time spent on each reason and status.

Last modified on 2023-10-22