w3resource

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.

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 '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:

SQL Subqueries Inventory Exercises: Find the average price of each manufacturer's products along with their name.

Practice Online


Sample Database:

Model Database

Query Visualization:

Duration:

Query visualization of Find the average price of each manufacturer's products along with their name - Duration

Rows:

Query visualization of Find the average price of each manufacturer's products along with their name - Rows

Cost:

Query visualization of Find the average price of each manufacturer's products along with their name - 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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.