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

Last update on November 02 2022 12:24:04 (UTC/GMT +8 hours)

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.

SQL: Tips of the Day

How to request a random row in SQL?

Select a random row with MySQL:

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

Select a random row with PostgreSQL:

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

Select a random row with Microsoft SQL Server:

SELECT TOP 1 column FROM table
ORDER BY NEWID()

Select a random row with IBM DB2:

SELECT column, RAND() as IDX 
FROM table 
ORDER BY IDX FETCH FIRST 1 ROWS ONLY

Select a random record with Oracle:

SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1

Database: SQL Server, PostgreSQL Server, MySQL

Ref: https://bit.ly/39n35HP

We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook