446,929 views
41 votes
41 votes
What is the SQL command to list the total sales by customer and by product, with subtotals by customer and a grand total for all product sales

User Parveez Ahmed
by
2.6k points

2 Answers

20 votes
20 votes

Final answer:

To generate a report with the total sales by customer and by product, including subtotals and a grand total, use the SQL GROUP BY clause with the ROLLUP function in conjunction with the SUM function.

Step-by-step explanation:

To list the total sales by customer and by product, including subtotals by customer and a grand total for all product sales using SQL, you would typically make use of the SUM function along with GROUP BY and ROLLUP (or CUBE) functions for generating subtotals and grand totals. Your SQL might look similar to this:

SELECT CustomerID, ProductID, SUM(SaleAmount) AS TotalSales
FROM Sales
GROUP BY ROLLUP (CustomerID, ProductID);

This SQL statement assumes you have a Sales table with columns for CustomerID, ProductID, and SaleAmount. The ROLLUP generates the hierarchical subtotal and grand total rows.

Note that different SQL database systems might have specific syntax or functions for these operations, so you would need to adjust the query according to the SQL dialect you are using, such as T-SQL for Microsoft SQL Server or PL/SQL for Oracle.

User YoniGeek
by
2.8k points
15 votes
15 votes

Answer:

Please find the complete query and question in the attached file.

Step-by-step explanation:

In this query, we use the select command in which we select the column that is "CUS_CODE, P_CODE" with the sum method that multiples the "SALE_UNITS * SALE_PRICE" values and use them as and group by the clause that stores all the values in the column that are "CUS_CODE, P_CODE WITH ROLLUP".

What is the SQL command to list the total sales by customer and by product, with subtotals-example-1
What is the SQL command to list the total sales by customer and by product, with subtotals-example-2
User Mortada Jafar
by
3.1k points