SuiteQL Query to Identify Items Frequently Sold Together

To identify items frequently sold together within a single sales order, the query performs two joins between the Transaction and TransactionLine tables. The first join (aliased as SalesOrderLine1) isolates orders containing a specified item, as defined in the WHERE clause. The second join (aliased as SalesOrderLine2) retrieves additional items included in those same orders.

To determine how often two items are sold together, the query groups the results by the second item’s attributes—ID, name, and type—and calculates the frequency using COUNT(*) AS TimesSoldTogether. The results are ordered by this count, ensuring that the most commonly paired items appear first.

A correlated subquery is used to return the total available quantity for each complementary item. This ensures that recommendations are limited to items currently in stock, aligning with the requirement to prioritize availability.

Additionally, the join to the complementary item (SalesOrderLine2) includes a filter to restrict results to specific item types—namely inventory items, non-inventory items, and services.

SELECT TOP 10
	SalesOrderLine2.Item AS Item,
	BUILTIN.DF( SalesOrderLine2.Item ) AS ItemName,
	BUILTIN.DF( SalesOrderLine2.ItemType ) AS ItemType,
	COUNT(*) AS TimesSoldTogether,
	( 
		SELECT 
			SUM( ItemInventoryBalance.QuantityAvailable ) 
		FROM 
			ItemInventoryBalance 
		WHERE 
			( ItemInventoryBalance.Item = SalesOrderLine2.Item ) 
	) AS QuantityAvailable
FROM
	Transaction AS SalesOrder
	INNER JOIN TransactionLine AS SalesOrderLine1 ON
		( SalesOrderLine1.Transaction = SalesOrder.ID )
	INNER JOIN TransactionLine AS SalesOrderLine2 ON
		( SalesOrderLine2.Transaction = SalesOrderLine1.Transaction )
		AND ( SalesOrderLine2.Item <> SalesOrderLine1.Item )
		AND ( SalesOrderLine2.ItemType IN ( 'InvtPart', 'NonInvtPart', 'Service' ) )
WHERE
	( SalesOrder.Type = 'SalesOrd' )
	AND ( SalesOrderLine1.Item = 99999 )	
GROUP BY
	SalesOrderLine2.Item,
	BUILTIN.DF( SalesOrderLine2.Item ),
	BUILTIN.DF( SalesOrderLine2.ItemType )
ORDER BY
	TimesSoldTogether DESC