Published on September 4, 2021.

This past week, a fellow NetSuite developer reached out to me for help with a SuiteQL query that she was developing. Her goal was to create a query that would list sales and purchase orders, with counts and totals for each combination of order type and order status. That's a relatively straightforward query to write. However, she also wanted to include an extra row in the results that would provide summaries by order type, and a "grand total" row that would include both order types.

Over the past few months, I've received several similar requests. So I thought I'd share what I believe is a very easy way to achieve this goal.

Summarizing Sales Orders by Status

Let's start by creating a simple query that summarizes sales orders by status.

SELECT
	BUILTIN.DF( Status ) AS StatusName,
	COUNT( * ) AS TransactionCount,
	SUM( ForeignTotal * ExchangeRate ) AS TotalAmount
FROM 
	Transaction 
WHERE 
	( Type = 'SalesOrd' )
GROUP BY
	BUILTIN.DF( Status )

Running that query in the SuiteQL Query Tool, in the NetSuite account that I'm currently working in, returns a response that looks like this.

As you can see, the query returns a row for every status that a sales order is in, with the number of orders in each status, and the total amount of the orders in each status.

A quick note about the total amount calculation: I'm multiplying the foreign amount of each order (the amount in the transaction's specific currency) by the currency exchange rate. This gives us the order's total amount in the account's base currency.

Adding A Summary Row Using ROLLUP

Let's add a summary row to the results. As I mentioned above, there's a very easy way to do it, and it involves using the SQL ROLLUP extension. ROLLUP is an extension to the GROUP BY clause, and we can use it like this.

SELECT
	BUILTIN.DF( Status ) AS StatusName,
	COUNT( * ) AS TransactionCount,
	SUM( ForeignTotal * ExchangeRate ) AS TotalAmount
FROM 
	Transaction 
WHERE 
	( Type = 'SalesOrd' )
GROUP BY
	ROLLUP ( BUILTIN.DF( Status ) )

All I've done is wrap the "BUILTIN.DF( Status )" calculation in the GROUP BY clause with the ROLLUP function. And here's what the result looks like.

Notice the additional row at the bottom of the table, which includes grand totals for all sales orders, regardless of their status.

Also notice that the "StatusName" column for that row is NULL. If you want to give that row a value for the "StatusName" column, you could use the COALESCE function, like this.

	
SELECT
	COALESCE( BUILTIN.DF( Status ), 'ALL STATUSES' ) AS StatusName,
	COUNT( * ) AS TransactionCount,
	SUM( ForeignTotal * ExchangeRate ) AS TotalAmount
FROM 
	Transaction 
WHERE 
	( Type = 'SalesOrd' )
GROUP BY
	ROLLUP ( BUILTIN.DF( Status )  )

And the results now look like this.

Including Purchase Orders In The Summary

Now let's adjust the query so that we're summarizing both sales and purchase orders.

SELECT
	COALESCE( Type, 'All Types' ) AS Type,
	COALESCE( BUILTIN.DF( Status ), ( 'All Statuses' ) ) AS StatusName,
	COUNT( * ) AS TransactionCount,
	SUM( ForeignTotal * ExchangeRate ) AS TotalAmount
FROM 
	Transaction 
WHERE
	( Type IN ( 'SalesOrd', 'PurchOrd' ) )
GROUP BY
	ROLLUP ( Type, BUILTIN.DF( Status )  )

Notice that I've added the transaction type to the columns being returned, and I'm using COALESCE so that "All Types" appears in the column for the summary row that reflects the total of all transaction types. I've also added the Type column to the ROLLUP function. And that leads me to an interesting point: You can use ROLLUP to summarize results across multiple dimensions. In this example, we're summarizing on both Type and Status, and the results look like this.

Notice the additional summary rows. There is now a subsummary for the Purchase Orders, a subsummary for the Sales Orders, and an overall summary for both transaction types.

Wrapping Up

There are other ways of achieveing the goal of adding summary rows to a query's results, but I'm not going to cover them here. The reason is that ROLLUP is, as the Oracle documents state, "highly efficient, adding minimal overhead to a query." So there's really no reason not to use it.

I hope you found this post interesting and helpful, and that you'll consider using the ROLLUP extension the next time you need it.