w3resource

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.


What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

ROW_NUMBER() in MySQL

SELECT t0.col3
FROM table AS t0
LEFT JOIN table AS t1 ON t0.col1=t1.col1 AND t0.col2=t1.col2 AND t1.col3>t0.col3
WHERE t1.col1 IS NULL;

Ref : https://bit.ly/3VX3Jzv