SQLite: How do I use the sum function

Question

The code I am using is here

CREATE TABLE timecard (
 id INTEGER PRIMARY KEY autoincrement,
 clock_in REAL not null,
 clock_out REAL,
 lunch integer,
 pay_type TEXT
);
INSERT INTO timecard VALUES(1,julianday("2021年02月15日 08:00"),julianday("2021年02月15日 16:00"),0,"HOLIDAY");
INSERT INTO timecard VALUES(2,julianday("2021年02月16日 07:23"),julianday("2021年02月16日 16:49"),1,"WORK");
INSERT INTO timecard VALUES(3,julianday("2021年02月17日 07:16"),julianday("2021年02月17日 16:05"),1,"WORK");
INSERT INTO timecard VALUES(4,julianday("2021年02月18日 07:54"),julianday("2021年02月18日 15:48"),1,"WORK");
.header on
.mode column
SELECT strftime('%W',clock_in) AS 'weekofyear',
 time(clock_in) as "IN",
 time(clock_out) as "OUT",
 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 as "total_time",
 sum("total_time")
FROM timecard
GROUP BY "weekofyear";

This is the output of the command.

weekofyear IN OUT total_time sum("total_time")
---------- ---------- ---------- ---------- -----------------
07 08:00:00 16:00:00 8.0 0.0

I am trying to get the total hours for the week of year. Not sure as to what I am doing wrong. I am new to the case statement in SQL and SQL in general.

Thanks for your time

Answer 1

You can't reference a column alias within the same SELECT clause as it was created in. In other words sum("total_time") 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.