Total in the End

To display the grand total at the end of the SQL query result, you can use the GROUP BY clause along with the WITH ROLLUP modifier. This will allow you to calculate the total for each group and also include a row with the grand total.

Here's an example query that demonstrates how to achieve this:

SELECT invoiceno, invoicedt, invoiceamt
FROM Sales
GROUP BY invoiceno, invoicedt, invoiceamt WITH ROLLUP;

In the above query, the GROUP BY clause is used to group the rows by invoiceno, invoicedt, and invoiceamt. The WITH ROLLUP modifier is added to include an additional row with the grand total.

The result of this query will include all the rows from the Sales table, grouped by invoiceno, invoicedt, and invoiceamt, along with a row at the end that displays the grand total.

Please use below query.

SELECT invoiceno, invoicedt, invoiceamt
FROM Sales
UNION ALL
SELECT 'Total', NULL, SUM(invoiceamt)
FROM Sales;

Thanks

Hello Ramco,

You can use this query to get the grand total as last row,

SELECT 
    invoiceno,
    invoicedt,
    invoiceamt
FROM 
    Sales

UNION ALL

SELECT 
    'Grand Total' AS invoiceno,
    NULL AS invoicedt,
    SUM(invoiceamt) AS invoiceamt
FROM 
    Sales;

To implement a Grand Total using the OVER clause, you can use the SUM() window function along with the PARTITION BY clause to calculate the running total for each row. Then, you can use a conditional statement to identify the Grand Total row. Here's an example:

SELECT
    invoiceno,
    invoicedt,
    CASE
        WHEN ROLLUP(invoiceno, invoicedt) IS NULL THEN SUM(invoiceamt) OVER ()
        ELSE invoiceamt
    END AS invoiceamt
FROM Sales

An alternative approach is to use the ROLLUP grouping set. The ROLLUP option is an extension to the GROUP BY clause and can be used to generate subtotals and grand totals for multiple levels of aggregation. Here's how you can modify your query to include a grand total using ROLLUP:

SELECT 
    CASE WHEN GROUPING(invoiceno) = 1 THEN 'Grand Total' ELSE invoiceno END AS invoiceno,
    MAX(invoicedt) AS invoicedt,
    SUM(invoiceamt) AS invoiceamt
FROM Sales
GROUP BY ROLLUP(invoiceno, invoicedt);

To display the Grand Total at the end of your query results, you can use the UNION or UNION ALL clause to combine the original query with an additional row containing the Grand Total. Here's an example:

-- Your original query
SELECT invoiceno, invoicedt, invoiceamt
FROM Sales

-- Union with Grand Total row
UNION ALL

-- Grand Total row
SELECT NULL AS invoiceno, NULL AS invoicedt, SUM(invoiceamt) AS invoiceamt
FROM Sales;