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
Sample table: Sales.SalesOrderDetail
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.
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
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises