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

# 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.

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