AdventureWorks Database: Return total sales and the discounts for each product

SQL Query - AdventureWorks: Exercise-194 with Solution

194. From the following tables write a query in SQL to return total sales and the discounts for each product. Sort the result set in descending order on productname.

Sample table: Production.Product

Click to view Full table

Sample table: Sales.SalesOrderDetail

Click to view Full table

Sample Solution:

SELECT p.Name AS ProductName, 
(OrderQty * UnitPrice) as NonDiscountSales,
((OrderQty * UnitPrice) * UnitPriceDiscount) as Discounts
FROM Production.Product AS p 
INNER JOIN Sales.SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID 
ORDER BY ProductName DESC;

Sample Output:

productname                    |nondiscountsales|discounts  |
-------------------------------+----------------+-----------+
Women's Tights, S              |         224.970|      0.000|
Women's Tights, S              |         134.982|      0.000|
Women's Tights, S              |         134.982|      0.000|
Women's Tights, S              |         359.952|      0.000|
Women's Tights, S              |         179.976|      0.000|
Women's Tights, S              |         224.970|      0.000|
Women's Tights, S              |         269.964|      0.000|
Women's Tights, S              |         179.976|      0.000|
Women's Tights, S              |         269.964|      0.000|
Women's Tights, S              |          44.994|      0.000|
Women's Tights, S              |        608.9188|  12.178376|
Women's Tights, S              |         179.976|      0.000|
Women's Tights, S              |         359.952|      0.000|
...

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Return with a product line of R and a days to manufacture less than 4.
Next: Calculate the revenue for each product in each sales order.