SuiteQL如何用子查询实现指定账户预算与交易金额的正确合计

我需要用SuiteQL制作一份自定义报表，按账户展示交易总金额，同时通过UNION ALL添加一行静态的TOTAL行。但当前合计结果是所有行的总和，我只需要统计指定行的合计值。

当前输出：

Account       | Amount
123 Account A | 10000
456 Account B | 20000
TOTAL         | 5000000

合计值5000000是所有金额行的总和

期望输出：

Account       | Amount
123 Account A | 10000
456 Account B | 20000
TOTAL         | 30000

合计值仅需统计“123 Account A”和“456 Account B”两行的总和

我当前使用CASE语句，仅在行满足ID条件时求和，但CASE仅作为条件判断，无法实现指定行的精准求和。我确定可以通过子查询实现——在外层SELECT中嵌套带WHERE子句的内层SELECT来筛选需要求和的行，但尝试时总是返回错误：

Search error occurred: Invalid or unsupported search

补充说明 #

以下是我当前的查询语句：

SELECT
  *
FROM
  (
    SELECT
      a.accountSearchDisplayName AS account_name,
      bi.periodamount3 AS budget_mar_25,
      SUM(
        CASE
          WHEN t.trandate BETWEEN TO_DATE ('03-01-2025', 'MM-DD-YYYY') AND TO_DATE  ('03-31-2025', 'MM-DD-YYYY') THEN tl.netamount
          ELSE 0
        END
      ) AS amount_march_25
    FROM
      transaction t
      JOIN transactionline tl ON t.id = tl.transaction
      JOIN account a ON tl.expenseaccount = a.id
      JOIN budgetimport bi ON a.id = bi.account
    WHERE
      a.id IN (866, 883)
      AND tl.subsidiary = 9
    GROUP BY
      a.accountSearchDisplayName,
      bi.periodamount3
    UNION ALL
    SELECT
      'TOTAL' AS account_name,
      SUM(
        CASE
          WHEN a.id IN (866, 883) THEN bi.periodamount3
          ELSE 0
        END
      ) AS budget_mar_25,
      SUM(
        CASE
          WHEN t.trandate BETWEEN TO_DATE ('03-01-2025', 'MM-DD-YYYY') AND TO_DATE  ('03-31-2025', 'MM-DD-YYYY') THEN tl.netamount
          ELSE 0
        END
      ) AS amount_march_25
    FROM
      transaction t
      JOIN transactionline tl ON t.id = tl.transaction
      JOIN account a ON tl.expenseaccount = a.id
      JOIN budgetimport bi ON a.id = bi.account
    WHERE
      a.id IN (866, 883)
      AND tl.subsidiary = 9
  )
ORDER BY
  CASE
    WHEN account_name = 'TOTAL' THEN 2
    ELSE 1
  END,
  account_name

我希望把这段代码替换为子查询，使其仅统计ID为866和883的账户的预算周期总和：

SUM(
  CASE
    WHEN a.id IN (866, 883) THEN bi.periodamount3
    ELSE 0
  END
) AS budget_mar_25,

补充说明2 #

使用上述CASE语句时，输出返回的是所有账户的预算总额，而非WHERE子句中筛选的两个账户的预算总额。

解决方案：基于子查询的精准合计方式 #

问题根源在于原查询UNION ALL的第二部分重复关联表时，可能因数据重复或冗余逻辑导致求和范围错误。更可靠的方式是先提取筛选后的账户明细，再基于明细数据计算合计。

方式1：使用CTE（SuiteQL多数版本支持）

WITH account_details AS (
  SELECT
    a.accountSearchDisplayName AS account_name,
    bi.periodamount3 AS budget_mar_25,
    SUM(
      CASE
        WHEN t.trandate BETWEEN TO_DATE ('03-01-2025', 'MM-DD-YYYY') AND TO_DATE ('03-31-2025', 'MM-DD-YYYY') THEN tl.netamount
        ELSE 0
      END
    ) AS amount_march_25
  FROM
    transaction t
    JOIN transactionline tl ON t.id = tl.transaction
    JOIN account a ON tl.expenseaccount = a.id
    JOIN budgetimport bi ON a.id = bi.account
  WHERE
    a.id IN (866, 883)
    AND tl.subsidiary = 9
  GROUP BY
    a.accountSearchDisplayName,
    bi.periodamount3
)
SELECT * FROM account_details
UNION ALL
SELECT
  'TOTAL' AS account_name,
  SUM(budget_mar_25) AS budget_mar_25,
  SUM(amount_march_25) AS amount_march_25
FROM account_details
ORDER BY
  CASE WHEN account_name = 'TOTAL' THEN 2 ELSE 1 END,
  account_name

方式2：嵌套子查询（兼容所有SuiteQL版本）

SELECT * FROM (
  SELECT
    a.accountSearchDisplayName AS account_name,
    bi.periodamount3 AS budget_mar_25,
    SUM(
      CASE
        WHEN t.trandate BETWEEN TO_DATE ('03-01-2025', 'MM-DD-YYYY') AND TO_DATE ('03-31-2025', 'MM-DD-YYYY') THEN tl.netamount
        ELSE 0
      END
    ) AS amount_march_25
  FROM
    transaction t
    JOIN transactionline tl ON t.id = tl.transaction
    JOIN account a ON tl.expenseaccount = a.id
    JOIN budgetimport bi ON a.id = bi.account
  WHERE
    a.id IN (866, 883)
    AND tl.subsidiary = 9
  GROUP BY
    a.accountSearchDisplayName,
    bi.periodamount3
) AS account_details
UNION ALL
SELECT
  'TOTAL' AS account_name,
  SUM(budget_mar_25) AS budget_mar_25,
  SUM(amount_march_25) AS amount_march_25
FROM (
  SELECT
    bi.periodamount3 AS budget_mar_25,
    SUM(
      CASE
        WHEN t.trandate BETWEEN TO_DATE ('03-01-2025', 'MM-DD-YYYY') AND TO_DATE ('03-31-2025', 'MM-DD-YYYY') THEN tl.netamount
        ELSE 0
      END
    ) AS amount_march_25
  FROM
    transaction t
    JOIN transactionline tl ON t.id = tl.transaction
    JOIN account a ON tl.expenseaccount = a.id
    JOIN budgetimport bi ON a.id = bi.account
  WHERE
    a.id IN (866, 883)
    AND tl.subsidiary = 9
  GROUP BY
    a.accountSearchDisplayName,
    bi.periodamount3
) AS account_details
ORDER BY
  CASE WHEN account_name = 'TOTAL' THEN 2 ELSE 1 END,
  account_name

方案说明 #

1. 先通过子查询/CTE筛选出指定账户的明细数据，确保数据范围准确。
2. 合计行直接基于筛选后的明细求和，避免重复关联表导致的错误。
3. 移除了原查询中冗余的CASE判断（WHERE已限定账户范围），简化逻辑。

内容的提问来源于stack exchange，提问作者Faiz Byputra