AdventureWorks Database: Find total ListPrice and StandardCost of products of distinct color
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
productid|name |productnumber|makeflag|finishedgoodsflag|color |safetystocklevel|reorderpoint|standardcost|listprice|size|sizeunitmeasurecode|weightunitmeasurecode|weight |daystomanufacture|productline|class|style|productsubcategoryid|productmodelid|sellstartdate |sellenddate |discontinueddate|rowguid |modifieddate |
---------+--------------------------------+-------------+--------+-----------------+------------+----------------+------------+------------+---------+----+-------------------+---------------------+-------+-----------------+-----------+-----+-----+--------------------+--------------+-----------------------+-----------------------+----------------+------------------------------------+-----------------------+
1|Adjustable Race |AR-5381 |false |false | | 1000| 750| 0| 0| | | | | 0| | | | | |2008-04-30 00:00:00.000| | |694215b7-08f7-4c0d-acb1-d734ba44c0c8|2014-02-08 10:01:36.827|
2|Bearing Ball |BA-8327 |false |false | | 1000| 750| 0| 0| | | | | 0| | | | | |2008-04-30 00:00:00.000| | |58ae3c20-4f3a-4749-a7d4-d568806cc537|2014-02-08 10:01:36.827|
3|BB Ball Bearing |BE-2349 |true |false | | 800| 600| 0| 0| | | | | 1| | | | | |2008-04-30 00:00:00.000| | |9c21aed2-5bfa-4f18-bcb8-f11638dc2e4e|2014-02-08 10:01:36.827|
4|Headset Ball Bearings |BE-2908 |false |false | | 800| 600| 0| 0| | | | | 0| | | | | |2008-04-30 00:00:00.000| | |ecfed6cb-51ff-49b5-b06c-7d8ac834db8b|2014-02-08 10:01:36.827|
316|Blade |BL-2036 |true |false | | 800| 600| 0| 0| | | | | 1| | | | | |2008-04-30 00:00:00.000| | |e73e9750-603b-4131-89f5-3dd15ed5ff80|2014-02-08 10:01:36.827|
317|LL Crankarm |CA-5965 |false |false |Black | 500| 375| 0| 0| | | | | 0| |L | | | |2008-04-30 00:00:00.000| | |3c9d10b7-a6b2-4774-9963-c19dcee72fea|2014-02-08 10:01:36.827|
318|ML Crankarm |CA-6738 |false |false |Black | 500| 375| 0| 0| | | | | 0| |M | | | |2008-04-30 00:00:00.000| | |eabb9a92-fa07-4eab-8955-f0517b4a4ca7|2014-02-08 10:01:36.827|
-- more --
Sample Solution:
-- Selecting color, total list price, and total standard cost for products with names starting with 'Mountain'
SELECT Color, SUM(ListPrice), SUM(StandardCost)
-- From the Product table in the Production schema
FROM Production.Product
-- Filtering records where Color is not NULL, ListPrice is not zero, and the product name starts with 'Mountain'
WHERE Color IS NOT NULL
AND ListPrice != 0.00
AND Name LIKE 'Mountain%'
-- Grouping the results by Color
GROUP BY Color
-- Ordering the results by Color
ORDER BY Color;
Explanation:
- This SQL query retrieves the total list price and total standard cost for products with names starting with 'Mountain', grouped by color.
- The SELECT clause specifies the columns to be retrieved: Color, the sum of ListPrice, and the sum of StandardCost.
- The FROM clause indicates the source table, which is Product in the Production schema.
- The WHERE clause filters the records based on specific conditions:
- Color IS NOT NULL: Ensures that only records with non-null color values are included.
- ListPrice != 0.00: Filters out products with a list price of zero.
- Name LIKE 'Mountain%': Selects only products whose names start with 'Mountain'.
- The GROUP BY clause groups the results by Color, so the aggregate functions (SUM) are applied to each distinct color category.
- The ORDER BY clause arranges the output by Color in ascending order.
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.