I want to create a custom report using SuiteQL. I want to show the total amount from the transaction based on its account. I need to sum up the rows that I gathered from the transaction table. So I am using UNION ALL to add a static row and make it titled TOTAL. But the sum returns all of the rows available, but in this case, I just want to show the sum of certain rows.

Current output:

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

The total return is 5000000 because it is the sum of all amount rows

Expected output:

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

The total needs to sum up only the row "123 Account A" and "456 Account B"

My current condition is using CASE to sum up the rows WHEN the condition of the row met (id). But I know the CASE is only used as a conditional statement whether the sum should show or not, not to sum up certain rows.

I am sure I could do it with subquery so I could use a SELECT statement with a WHERE clause to filter the rows I need to sum inside another SELECT.

How can I achieve this? Since subquery always returns an invalid search.

Search error occurred: Invalid or unsupported search

Thanks in advance.

Edit

Here is my query:

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 tlamount
          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 tlamount
          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

I want to change:

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

Into subquery so it can SELECT budget period sum from those in 866 and 883 only.

Edit 2

Here's the output when I use case:

It returns the total amount of all accounts budget instead of only two accounts budget I have filtered in the WHERE statement.

I want to create a custom report using SuiteQL. I want to show the total amount from the transaction based on its account. I need to sum up the rows that I gathered from the transaction table. So I am using UNION ALL to add a static row and make it titled TOTAL. But the sum returns all of the rows available, but in this case, I just want to show the sum of certain rows.

Current output:

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

The total return is 5000000 because it is the sum of all amount rows

Expected output:

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

The total needs to sum up only the row "123 Account A" and "456 Account B"

My current condition is using CASE to sum up the rows WHEN the condition of the row met (id). But I know the CASE is only used as a conditional statement whether the sum should show or not, not to sum up certain rows.

I am sure I could do it with subquery so I could use a SELECT statement with a WHERE clause to filter the rows I need to sum inside another SELECT.

How can I achieve this? Since subquery always returns an invalid search.

Search error occurred: Invalid or unsupported search

Thanks in advance.

Edit

Here is my query:

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 tlamount
          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 tlamount
          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

I want to change:

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

Into subquery so it can SELECT budget period sum from those in 866 and 883 only.

Edit 2

Here's the output when I use case:

It returns the total amount of all accounts budget instead of only two accounts budget I have filtered in the WHERE statement.

• oracle-database
• netsuite
• suitescript
• suiteql

Share Improve this question Follow edited Mar 16 at 6:31 Faiz Byputra asked Mar 13 at 2:06 Faiz ByputraFaiz Byputra 7822 silver badges88 bronze badges 4

• Please show us your query. – Wernfried Domscheit Commented Mar 13 at 5:28
• I have provided the query in the edit @WernfriedDomscheit – Faiz Byputra Commented Mar 13 at 6:38
• Why do you want to do a subquery? The case statement to do a mini-pivot table is perfectly reasonable. Note that the case statement is currently redundant since all your where clauses limit the accounts to what you have in the case statement – bknights Commented Mar 14 at 16:44
• Because I see that the case statement does not filtering the amount total. it returns the total sum amount of all accounts instead of the two accounts only. I have updated the answer and provide the query output @bknights – Faiz Byputra Commented Mar 16 at 6:33

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

You could use the ROLLUP clause:

WITH t(ACCOUNT, amount) AS (
    SELECT '123 Account A', 4000 FROM dual UNION ALL
    SELECT '123 Account A', 6000 FROM dual UNION ALL
    SELECT '456 Account B', 15000 FROM dual UNION ALL
    SELECT '456 Account B', 5000 FROM dual
)
SELECT NVL(ACCOUNT, 'Total') AS ACCOUNT, SUM(amount)
FROM t
GROUP BY ROLLUP (ACCOUNT);

Output:

+-------------------------+
|ACCOUNT      |SUM(AMOUNT)|
+-------------------------+
|123 Account A|10000      |
|456 Account B|20000      |
|Total        |30000      |
+-------------------------+