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

Last update on February 10 2025 06:28:04 (UTC/GMT +8 hours)

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

For more Practice: Solve these Related Problems:

• Write a SQL query to calculate the total price of items for each company and return the company code and total price.
• Write a SQL query to find the maximum price of items for each manufacturer and return the company code and maximum price.
• Write a SQL query to compute the minimum price of products for each company and display the company code and minimum price.
• Write a SQL query to determine the sum of prices for all items produced by each manufacturer, along with the company code.

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