Items: Frequently Sold Together

Returns a list of items that are frequently sold along with a specified item. This can be used to implement the "cross-selling" sales technique.

-- Contributor: Tim Dietrich (timdietrich@me.com)
-- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-items-frequently-sold-together/
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

Click here to return to the list of available queries.