w3resource

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 Expression: Display the average price of the items for each company, showing only the company code.

Relational Algebra Tree:

Relational Algebra Tree: Display the average price of the items for each company, showing only the company code.

Practice Online


Query Visualization:

Duration:

Query visualization of Display the average price of the items for each company, showing only the company code - Duration

Rows:

Query visualization of Display the average price of the items for each company, showing only the company code - Rows

Cost:

Query visualization of Display the average price of the items for each company, showing only the company code - Cost

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

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

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.