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

Click to view Full table

Sample table: Sales.SalesOrderDetail

Click to view Full table

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.