SQLite：如何使用 sum 函式

我使用的程式碼在這裡

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";

這是命令的輸出。

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

我正在嘗試獲取一年中一周的總小時數。不確定我做錯了什麼。我對 SQL 和 SQL 中的 case 語句很陌生。

謝謝你的時間

您不能在SELECT創建它的同一子句中引用列別名。換句話說sum("total_time")，正在引用尚不存在的東西。您需要將之前的CASE語句複製到您的SUM()函式呼叫中，如下所示：

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
)

或者先使用子查詢之類的東西來生成total_time列，然後從子查詢SUM()中將該函式應用於該列的頂部。

引用自：https://dba.stackexchange.com/questions/285681