RE: Can I use subquery in SuiteQL?
Hi, I want to create a custom report using SuiteQL. 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 row available, but in this case, I just want to show the sum of certain rows. I am sure I could do it with subquery so I could use SELECT statement with a WHERE clause to filter the rows I need to sum inside another SELECT.
Expected output:
Account | Budget 123 | 10000 456 | 20000 TOTAL | 30000
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 conditional statement whether the sum should show or not, not to sum up certain rows.
How can I achieve this?
It would be good to see your query, but yes you can subquery in either condition or result
SELECT id, (SELECT altname FROM customer WHERE customer.id=transaction.entity) FROM transaction WHERE entity IN( SELECT id FROM customer WHERE altname LIKE '%Paul%' )
You might want to make a virtual table instead, though, something like
SELECT a.id FROM (SELECT id FROM sometable WHERE x=y) as a
If you can share your actual query, it would probably be a tad easier, but in short, yes, you should be able to subquery as needed.
