Published on February 16, 2025.

I recently had a NetSuite client ask for an interesting sales-related SuiteQL query. They requested a query that will return a list of items that are frequently sold with a specified item.

They had several goals that they were hoping to achieve with the query, including:
• When entering quotes or sales orders, show users other items that are frequently sold with an item that was on the order.
• When displaying items via their custom Customer Web Portal, show customers other items that were often sold with the item being viewed.

In both cases, the underlying goal was to be able to intelligently propose other items that the customer might be interested in - a sales technique often referred to as cross-selling.

Here's the query that I provided to the client.

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

Here are a few notes about the query...

To get the items sold together on a single order, I'm joining from the Transaction table to the TransactionLine table, and I'm doing so twice. The first time (as alias SalesOrderLine1) is to get orders on which the specified item appears (which is specified in the WHERE clause). The second time (as alias SalesOrderLine2) is to get other items on the same sales order.

To get the number of times that two items were sold together, I'm grouping the resulting rows by the second item's information (ID, name, and type) and get a count of the number of times the item pairs were sold together (which is represented in the SELECT clause as "COUNT(*) AS TimesSoldTogether"). I'm ordering the resulting rows based on that count, so the items sold most frequently together appear first in the results.

I'm using a correlated subquery to also return the total quantities available for the items. The thought here is that there's probably no point in recommending a complementary item if it isn't available from stock.

And finally, notice that in the JOIN to the complementary item (aliased as SalesOrderLine2) I'm using a filter so that only specific types of items (inventory items, non-inventory items, and services) are returned. This was at the request of my client. You could, of course, remove the filter to return items of all types, or modify it so that additional types of items (such as kits) are returned.

As always, I hope you find the query shared in the post to be helpful.