w3resource

AdventureWorks Database: Find total ListPrice and StandardCost of products of distinct color

SQL Query - AdventureWorks: Exercise-98 with Solution

98. From the following table write a query in SQL to return the total ListPrice and StandardCost of products for each color. Products that name starts with 'Mountain' and ListPrice is more than zero. Return Color, total list price, total standardcode. Sort the result set on color in ascending order.

Sample table: production.Product


Click to view Full table

Sample Solution:

SELECT Color, SUM(ListPrice), SUM(StandardCost)  
FROM Production.Product  
WHERE Color IS NOT NULL   
    AND ListPrice != 0.00   
    AND Name LIKE 'Mountain%'  
GROUP BY Color  
ORDER BY Color;

Sample Output:

color |sum     |sum       |
------+--------+----------+
Black |27404.84|15214.9616|
Silver|26462.84|14665.6792|
White |    19.0|    6.7926|

SQL AdventureWorks Editor:

Practice Online


Contribute your code and comments through Disqus.

Previous: Populate the variance of all unique values and all values.
Next: Find the TotalSalesYTD of each SalesQuota.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Share this Tutorial / Exercise on : Facebook and Twitter

SQL: Tips of the Day

Convert Timestamp to date in MySQL Query:

DATE_FORMAT(FROM_UNIXTIME(`user.registration`), '%e %b %Y') AS 'date_formatted'

Database: MySQL

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