AdventureWorks Database: Calculate the revenue for each product in each sales order
SQL Query - AdventureWorks: Exercise-195 with Solution
195. From the following tables write a query in SQL to calculate the revenue for each product in each sales order. Sort the result set in ascending order on productname.
Sample table: Production.Product
Sample table: Sales.SalesOrderDetail
Sample Solution:
SELECT 'Total income is', ((OrderQty * UnitPrice) * (1.0 - UnitPriceDiscount)), ' for ',
p.Name AS ProductName
FROM Production.Product AS p
INNER JOIN Sales.SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID
ORDER BY ProductName ASC;
Sample Output:
?column? |?column? |?column?|productname | ---------------+------------+--------+--------------------------------+ Total income is| 159.0| for |All-Purpose Bike Stand | Total income is| 159.0| for |All-Purpose Bike Stand | Total income is| 159.0| for |All-Purpose Bike Stand | Total income is| 159.0| for |All-Purpose Bike Stand | Total income is| 159.0| for |All-Purpose Bike Stand | Total income is| 159.0| for |All-Purpose Bike Stand | Total income is| 159.0| for |All-Purpose Bike Stand | Total income is| 159.0| for |All-Purpose Bike Stand | Total income is| 159.0| for |All-Purpose Bike Stand | Total income is| 159.0| for |All-Purpose Bike Stand | Total income is| 159.0| for |All-Purpose Bike Stand | Total income is| 159.0| for |All-Purpose Bike Stand | Total income is| 159.0| for |All-Purpose Bike Stand | ...
Contribute your code and comments through Disqus.
Previous: Return total sales and the discounts for each product.
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