SQL Exercises: Find the average price of each manufacturer products

SQL SUBQUERY: Exercise-31 with Solution

31. From the following tables write a SQL query to calculate the average price of each manufacturer's product along with their name. Return Average Price and Company.

COM_ID COM_NAME
------ -------------
    11 Samsung
    12 iBall
    13 Epsion
    14 Zebronics
    15 Asus
    16 Frontech

 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 'pro_price' as "Average Price" and 'com_name' from the 'company_mast' table as "Company"
SELECT AVG(pro_price) AS "Average Price", 
   company_mast.com_name As "Company"
-- Specifying the tables to retrieve data from ('item_mast' and 'company_mast') and defining the relationship between them
FROM item_mast, company_mast
-- Filtering the results based on the condition that 'pro_com' in 'item_mast' matches 'com_id' in 'company_mast'
WHERE item_mast.pro_com = company_mast.com_id
-- Grouping the results by 'com_name' from 'company_mast'
GROUP BY company_mast.com_name;

Output of the Query:

Average Price		Company
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 products and the company name from two tables 'item_mast' and 'company_mast'. The query is grouping the results by company name, so the average price of each company's products will be displayed separately. The query is joining the two tables on the pro_com column of the item_mast table, and the com_id column of the company_mast table to get the company name for each product. It also renaming the calculated column "AVG(pro_price)" as "Average Price" and "company_mast.com_name" as "Company".

Visual Explanation:

Practice Online

Sample Database:

Query Visualization:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous SQL Exercise: Different grades than other Dallas customers.
Next SQL Exercise: List of products with an average price of 350 or more.