SQL Exercise: Display the average price of items of each company
23. Average Price by Company
From the following tables write a SQL query to calculate the average price of items of each company. Return average value and company name.
Sample table: company_mast
COM_ID COM_NAME
------ -------------
11 Samsung
12 iBall
13 Epsion
14 Zebronics
15 Asus
16 Frontech
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:
-- Selecting the average of 'pro_price' and the company name from the result of an inner join between 'item_mast' and 'company_mast' tables
SELECT AVG(pro_price), company_mast.com_name
-- Specifying the tables to retrieve data from ('item_mast' as the left table and 'company_mast' as the right table)
FROM item_mast
-- Performing an inner join based on the equality of 'pro_com' in 'item_mast' and 'com_id' in 'company_mast'
INNER JOIN company_mast
ON item_mast.pro_com = company_mast.com_id
-- Grouping the results by the company name
GROUP BY company_mast.com_name;
Output of the Query:
avg com_name 5000.0000000000000000 Samsung 650.0000000000000000 iBall 1475.0000000000000000 Epsion 500.0000000000000000 Frontech 250.0000000000000000 Zebronics 3200.0000000000000000 Asus
Explanation:
The said SQL query is selecting the average price of all products (AVG(pro_price)) and the company name (com_name) from the tables item_mast and company_mast, and grouping the results by the company name.
The query is using an INNER JOIN to combine the data from the two tables, linking the 'pro_com' column in item_mast to the 'com_id' column in company_mast.
Relational Algebra Expression:
Relational Algebra Tree:
Practice Online
Query Visualization:
Duration:
Rows:
Cost:
For more Practice: Solve these Related Problems:
- Write a SQL query to join company_mast and item_mast, group by com_name, and calculate the average product price for each company.
- Write a SQL query to display each company’s name along with the average price of its products, rounded to two decimal places.
- Write a SQL query to compute the average item price per company using GROUP BY and the AVG() function.
- Write a SQL query to list company names with their corresponding average product price, filtering out any items with negative prices.
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Display the item name, price, and company name.
Next SQL Exercise: Company whose products have an average price.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.