SQLite: How do I use the sum function 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(&quot;2021-02-15 08:00&quot;),julianday(&quot;2021-02-15 16:00&quot;),0,&quot;HOLIDAY&quot;);
INSERT INTO timecard VALUES(2,julianday(&quot;2021-02-16 07:23&quot;),julianday(&quot;2021-02-16 16:49&quot;),1,&quot;WORK&quot;);
INSERT INTO timecard VALUES(3,julianday(&quot;2021-02-17 07:16&quot;),julianday(&quot;2021-02-17 16:05&quot;),1,&quot;WORK&quot;);
INSERT INTO timecard VALUES(4,julianday(&quot;2021-02-18 07:54&quot;),julianday(&quot;2021-02-18 15:48&quot;),1,&quot;WORK&quot;);

.header on
.mode column

SELECT strftime('%W',clock_in) AS 'weekofyear',
       time(clock_in) as &quot;IN&quot;,
       time(clock_out) as &quot;OUT&quot;,
       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 &quot;total_time&quot;,
       sum(&quot;total_time&quot;)
FROM timecard
GROUP BY &quot;weekofyear&quot;;

This is the output of the command.
weekofyear  IN          OUT         total_time  sum(&quot;total_time&quot;)
----------  ----------  ----------  ----------  -----------------
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
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.