SQL Exercises: Display the average price of the items for each company

SQL Basic Select Statement: Exercise-29 with Solution.

From the following table, write a SQL query to calculate average price of the items for each company. Return average price and company code.

Sample table: item_mast

 PRO_ID PRO_NAME                       PRO_PRICE    PRO_COM
------- ------------------------- -------------- ----------
    101 Mother Board                    3200.00         15
    102 Key Board                        450.00         16
    103 ZIP drive                        250.00         14
    104 Speaker                          550.00         16
    105 Monitor                         5000.00         11
    106 DVD drive                        900.00         12
    107 CD drive                         800.00         12
    108 Printer                         2600.00         13
    109 Refill cartridge                 350.00         13
    110 Mouse                            250.00         12

Sample Solution:

-- This query calculates the average value of the 'pro_price' column for each distinct value of 'pro_com'.
SELECT AVG(pro_price), pro_com
-- Specifies the table from which to retrieve the data (in this case, 'item_mast').
FROM item_mast
-- Groups the result set by the 'pro_com' column.
GROUP BY pro_com;

Output of the Query:

avg					pro_com
250.0000000000000000			14
650.0000000000000000			12
3200.0000000000000000			15
5000.0000000000000000			11
1475.0000000000000000			13
500.0000000000000000			16

Code Explanation:

The above query in SQL that selects the average price of each product by company pro_com, from the table item_mast. In order to calculate the average price, the results are grouped by company (pro_com) and the average price is calculated using the AVG() function.

Relational Algebra Expression:

Relational Algebra Tree:

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Items with a price over 250 in ASC order by price,name.
Next SQL Exercise: Find the name and price of the cheapest item(s).