AdventureWorks Database: Populate the variance of all unique values and all values

Last update on December 20 2024 11:58:09 (UTC/GMT +8 hours)

97. From the following table write a query in SQL to populate the variance of all unique values as well as all values, including any duplicates values of SalesQuota column.

Sample table: sales.salespersonquotahistory

businessentityid|quotadate              |salesquota|rowguid                             |modifieddate           |
----------------+-----------------------+----------+------------------------------------+-----------------------+
             274|2011-05-31 00:00:00.000|     28000|99109bbf-8693-4587-bc23-6036ec89e1be|2011-04-16 00:00:00.000|
             274|2011-08-31 00:00:00.000|      7000|dfd01444-8900-461c-8d6f-04598dae01d4|2011-07-17 00:00:00.000|
             274|2011-12-01 00:00:00.000|     91000|0a69f453-9689-4ccf-a08c-c644670f5668|2011-10-17 00:00:00.000|
             274|2012-02-29 00:00:00.000|    140000|da8d1458-5fb9-4c3e-9ead-8f5ce1393047|2012-01-15 00:00:00.000|
             274|2012-05-30 00:00:00.000|     70000|760cef84-b980-417b-a667-7358c38857f0|2012-04-15 00:00:00.000|
             274|2012-08-30 00:00:00.000|    154000|fb29e024-f26a-49aa-a7cc-c99ae7ba4853|2012-07-16 00:00:00.000|
             274|2012-11-30 00:00:00.000|    107000|13947d2c-a254-47c9-8817-cbd186ffa526|2012-10-16 00:00:00.000|
             274|2013-02-28 00:00:00.000|     58000|bc1a222f-47a0-48d1-9c56-ac873269dc98|2013-01-14 00:00:00.000|
             274|2013-05-30 00:00:00.000|    263000|8b4e3cbf-f0ef-49c5-9a8c-87679055057e|2013-04-15 00:00:00.000|
             274|2013-08-30 00:00:00.000|    116000|e9de95e3-b119-4441-bd1d-b27fc4516022|2013-07-16 00:00:00.000|
             274|2013-11-30 00:00:00.000|     84000|7cea47b5-8391-4414-a866-ff6ec6628cd3|2013-10-16 00:00:00.000|
             274|2014-03-01 00:00:00.000|    187000|0cfb6474-20aa-46d3-a5c0-29dba2eda025|2014-01-15 00:00:00.000|
-- more --

Click to view Full table

Sample Solution:

-- Calculating the population variance for distinct and all values of SalesQuota
SELECT var_pop(DISTINCT SalesQuota) AS Distinct_Values, var_pop(SalesQuota) AS All_Values  
-- From the salesperson quota history table
FROM sales.salespersonquotahistory;

Explanation:

This SQL query computes the population variance for distinct and all values of the SalesQuota column.
The var_pop() function is used to calculate the population variance.
Two variants of the function are applied: one for distinct values (DISTINCT SalesQuota) and one for all values of SalesQuota.
Results are obtained from the salespersonquotahistory table, which presumably contains historical sales quota data.
There is no WHERE clause, so the query retrieves variance statistics for all records in the table.

Sample Output:

distinct_values      |all_values           |
---------------------+---------------------+
158146830494.18114353|157788848582.93499944|

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Compute the variance of sales quota for each quarter in a year.
Next: Find total ListPrice and StandardCost of products of distinct color.