correct the following suiteql query, which should return the total amount per item (without tax) of all sales orders for a customer with id 96, in october 2024:

SELECT item AS item_id, SUM(transactionline.amount) AS total FROM transactionline, transaction WHERE transaction.type = 'SalesOrd' AND transaction.entity = 97 AND transaction.trandate BETWEEN TO_DATE('2024-10-01', 'YYYY-MM-DD') AND TO_DATE('2024-11-01', 'YYYY-MM-DD') GROUP BY item ORDER BY item_id

the query as it is is very slow and returns the wrong info. optimize where necessary

AI generated answer (using internal Slack threads):

The provided SuiteQL query seems to have a few issues. First, it's querying for the wrong customer ID (97 instead of 96). Second, it's joining two large tables without specifying a join condition, which can lead to a Cartesian product and slow down the query. Third, it's not filtering out tax lines, which can distort the total amount per item. Here's a corrected and optimized version of the query:

sql
SELECT 
    tl.item AS item_id, 
    SUM(tl.amount) AS total 
FROM 
    transaction t
INNER JOIN 
    transactionline tl 
ON 
    t.id = tl.transaction 
WHERE 
    t.type = 'SalesOrd' 
    AND t.entity = 96 
    AND t.trandate BETWEEN TO_DATE('2024-10-01', 'YYYY-MM-DD') AND TO_DATE('2024-11-01', 'YYYY-MM-DD') 
    AND tl.taxline = FALSE
GROUP BY 
    tl.item 
ORDER BY 
    tl.item

This query joins the

transaction

and

transactionline

tables on their common

id

field, filters for sales orders (

SalesOrd

) for the customer with ID 96, and only includes transactions from October 2024. It also excludes tax lines from the sum of amounts. The results are grouped by item and ordered by item ID.