Just one of those things you may not know existed - the ROLLUP clause. It's very useful for totaling. Are you used to doing something like this?
SELECT customer, sum(sales) from table
UNION
SELECT 'Total', sum(sales) from table
The problem here is that on larger datasets, this can really be a pain in terms of performance. ROLLUP can be extremely quick, but the syntax may not be straight forward. Here's an example of summing sales in AdventureWorks2017 with a Grand Total.
USE AdventureWorks2017
SELECT
SalesOrderID,
sum(LineTotal)
FROM Sales.SalesOrderDetail
where SalesOrderID in (
'43659',
'43660',
'43661',
'43662',
'43663'
)
GROUP BY SalesOrderID
WITH ROLLUP
Notice I've added a WITH ROLLUP at the end of my GROUP BY. This creates the grand total. However, I'm left with a NULL for the SalesOrderID. To solve this I wouldn't want to use a coalesce, because a NULL might be valid elsewhere. Instead we should use the GROUPING function.
USE AdventureWorks2017
SELECT
CASE
WHEN GROUPING(SalesOrderID) = 1 THEN 'Total'
ELSE cast(SalesOrderID as nvarchar(10))
END as SalesOrderID,
sum(LineTotal)
FROM Sales.SalesOrderDetail
where SalesOrderID in (
'43659',
'43660',
'43661',
'43662',
'43663'
)
GROUP BY SalesOrderID
WITH ROLLUP
You can extend this even further...