Oracle SQL 求和並取最大日期：去掉 GROUP BY 使用 MAX 聚合函數

原查詢

SELECT SUM(t), da
FROM ( ... UNION ALL ... )  -- 多行測試數據
GROUP BY da;  -- 錯誤：按日期分組導致每行單獨求和

正確寫法

SELECT SUM(t) AS total_sum, MAX(da) AS max_date
FROM (
    SELECT 10 AS t, DATE '2013-11-12' AS da FROM dual UNION ALL
    SELECT 10, DATE '2013-11-13' FROM dual UNION ALL
    SELECT 10, DATE '2013-11-14' FROM dual UNION ALL
    SELECT 10, DATE '2013-11-15' FROM dual UNION ALL
    SELECT 10, DATE '2013-11-16' FROM dual UNION ALL
    SELECT 10, DATE '2013-11-19' FROM dual UNION ALL
    SELECT 10, DATE '2013-11-11' FROM dual
);

結果

TOTAL_SUM  MAX_DATE
---------  ----------
        70  2013-11-19

要點

• 聚合查詢無 GROUP BY 時，整張表為一組，SUM、MAX 等直接作用於所有行。
• 日期字面量建議用 DATE 'YYYY-MM-DD' 避免隱式轉換錯誤。
• 若需按某列分組再求和/取最大，才加 GROUP BY。