Query and total for each week

Question

user, timeon, timeoff, hours 
Monday - 
1510, 2021-10-25 05:00:00, 2021-10-25 09:00:00 - 
1510, 2021-10-25 14:00:00, 2021-10-25 17:00:00 - 

The total should be 4 + 3 = 7

Tuesday - 
1510, 2021-10-25 05:00:00, 2021-10-25 09:00:00 - 
1510, 2021-10-25 14:00:00, 2021-10-25 17:00:00 -

The total should be 4 + 3 = 7, then total at the end of the week

I have a user, timeon, timeoff for each day as they might be on and off more than once. I have a total at the bottom datediff(minute, timeon, timeoff). I'm using Firebird 3.

I have a virtual table

with M as
(
    select user, timeon, timeoff, hours
    from (select userid, timeon, timeoff,
    datediff(minute,timeon, timeoff)/60.00 as hours
    from sessions s
    where extract(weekday from timeon)=1
    and userID=1
    and cast(logon as date)='2021-10-28')
)
select * from M
union all
select '','','',sum(hours) from M

Now, I need to repeat the above but for a Tuesday, then Wednesday and so on. I can't start with another v table nor a union. What am I doing wrong?

Answer 1

As has been mentioned in the request comments, we would usually use ROLLUP or GROUPING SETS, but Firebird doesn't support these.

Using a WITH clause instead is a good idea. Here is a query with the sub totals and a final total:

with s as
(
  select
    extract(weekday from timeon) as day,
    userid, timeon, timeoff,
    datediff(minute, timeon, timeoff) / 60.00 as hours
  from sessions
  -- where userid = 1
)
select * from s
union all
select day, userid, null, null, sum(hours) from s group by day, userid
union all
select day, null, null, null, sum(hours) from s group by day
union all
select null, null, null , null, sum(hours) from s
order by day nulls last, userid nulls last, timeon nulls last;

This is not precise, as we would count a session from 10 pm to 2 am as four hours on the starting day, rather than 2 hours the starting day and 2 hours the following day. But you did the same in your query, so I guess this is fine with you.