Total in the End

Question

Hi I have below query i want to display Grand Total in the end select invoiceno , invoicedt , invoiceamt from Sales Thanks

Naimish Makwana · Answer

Please use below query. SELECT invoiceno, invoicedt, invoiceamt FROM Sales UNION ALL SELECT 'Total', NULL, SUM(invoiceamt) FROM Sales; Thanks

Jignesh Kumar · Answer

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;

Jayraj Chhaya · Answer

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.

Prasad Raveendran · Answer

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

Prasad Raveendran · Answer

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);

Prasad Raveendran · Answer

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;