w3resource

SQL Exercise: Display the average price of items of each company

SQL JOINS: Exercise-23 with Solution

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

Sample table: item_mast

Sample Solution:

SELECT AVG(pro_price), company_mast.com_name
   FROM item_mast INNER 
     JOIN company_mast
        ON item_mast.pro_com= company_mast.com_id
           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 Expression: Display the average price of items of each company, showing the name of the company.

Relational Algebra Tree:

Relational Algebra Tree: Display the average price of items of each company, showing the name of the company.

Practice Online


Query Visualization:

Duration:

Query visualization of Display the average price of items of each company, showing the name of the company - Duration

Rows:

Query visualization of Display the average price of items of each company, showing the name of the company - Rows

Cost:

Query visualization of Display the average price of items of each company, showing the name of the company - Cost

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.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

Grouped LIMIT in PostgreSQL: Show the first N rows for each group?

db=# SELECT * FROM xxx;
 id | section_id | name
----+------------+------
  1 |          1 | A
  2 |          1 | B
  3 |          1 | C
  4 |          1 | D
  5 |          2 | E
  6 |          2 | F
  7 |          3 | G
  8 |          2 | H
(8 rows)

I need the first 2 rows (ordered by name) for each section_id, i.e. a result similar to:

id | section_id | name
----+------------+------
  1 |          1 | A
  2 |          1 | B
  5 |          2 | E
  6 |          2 | F
  7 |          3 | G
(5 rows)

PostgreSQL v9.3 you can do a lateral join

select distinct t_outer.section_id, t_top.id, t_top.name from t t_outer
join lateral (
    select * from t t_inner
    where t_inner.section_id = t_outer.section_id
    order by t_inner.name
    limit 2
) t_top on true
order by t_outer.section_id;

Database: PostgreSQL

Ref: https://bit.ly/3AfYwZI

 





We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook