SQLite: How do I use the sum function
You can't reference a column alias within the same SELECT clause as it was created in. In other words sum(&quot;total_time&quot;) is referencing something that doesn't exist yet. You'll either need to copy your previous CASE statement into your SUM() function call like this:
SUM
(
    CASE when lunch = 1 then round(Cast (( JulianDay(clock_out) - JulianDay(clock_in)) * 24 - .5 As REAL),2)
         when lunch = 0 then round(Cast (( JulianDay(clock_out) - JulianDay(clock_in)) * 24 As REAL),2)
    END
)

Or use something like a subquery to generate the total_time column first, then apply the SUM() function on top of that column from your subquery.