w3resource

AdventureWorks Database: Find the average list price of unique products

SQL Query - AdventureWorks: Exercise-87 with Solution

87. From the following table write a query in SQL to return the average list price of products. Consider the calculation only on unique values.

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

Click to view Full table

Sample Solution:

SELECT AVG(DISTINCT ListPrice)  
FROM Production.Product;

Sample Output:

avg                 |
--------------------+
437.4042718446601942|

SQL AdventureWorks Editor:

Practice Online


Contribute your code and comments through Disqus.

Previous: Find average bonus and total SalesYTD for each territory.
Next: Compute moving average of yearly sales for each territory.

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.