w3resource

SQL Challenges-1: Average Selling Price

SQL Challenges-1: Exercise-44 with Solution

From the following tables write a SQL query to find the average selling price for each item. Return item code and average_selling_price. average_selling_price should be rounded to 2 decimal places.

Input:

Table: item_price

Structure:

FieldTypeNullKeyDefaultExtra
item_codeint(11)YES
date_fromdateYES
date_todateYES
item_costint(11)YES

Data:

item_codedate_fromdate_toitem_cost
1012018-04-072018-06-288
1022018-02-152018-04-1713
1032018-03-122018-04-3010
1012018-06-292018-10-3115
1032018-05-012019-08-2414
1022018-04-182018-07-1025
1042018-06-112018-10-1025
1012018-11-012019-01-1520

Table: sale

Structure:

FieldTypeNullKeyDefaultExtra
sale_datedateYES
item_codeint(11)YES
sale_qtyint(11)YES

Data:

sale_dateitem_codesale_qty
2018-05-15101120
2018-04-2710380
2018-04-10102200
2018-07-12101100
2018-07-0710350
2018-09-17104100
2018-06-25102100

Sample Solution:

SQL Code(MySQL):

Create table  item_price (item_code integer, date_from date, date_to date, item_cost integer);
Create table  sale (sale_date date,item_code integer,sale_qty integer);


insert into item_price  values (101, '2018-04-07', '2018-06-28', 8);
insert into item_price  values (102, '2018-02-15', '2018-04-17', 13);
insert into item_price  values (103, '2018-03-12', '2018-04-30', 10);
insert into item_price  values (101, '2018-06-29', '2018-10-31', 15);
insert into item_price  values (103, '2018-05-01', '2019-08-24', 14);
insert into item_price  values (102, '2018-04-18', '2018-07-10', 25);
insert into item_price  values (104, '2018-06-11', '2018-10-10', 25);
insert into item_price  values (101, '2018-11-01', '2019-01-15', 20);



insert into sale  values ('2018-05-15',101,120);
insert into sale  values ('2018-04-27',103, 80);
insert into sale  values ('2018-04-10',102, 200);
insert into sale  values ('2018-07-12',101, 100);
insert into sale  values ('2018-07-07',103, 50);
insert into sale  values ('2018-09-17',104, 100);
insert into sale  values ('2018-06-25',102, 100);



SELECT sl.item_code, 
ROUND(SUM(sl.sale_qty*ip.item_cost)/SUM(sl.sale_qty),2) AS average_selling_price
FROM sale sl
LEFT JOIN item_price ip 
ON sl.item_code = ip.item_code 
AND sl.sale_date BETWEEN ip.date_from AND ip.date_to
GROUP BY item_code;

Sample Output:

item_code|average_selling_price|
---------|---------------------|
      101|                11.18|
      102|                17.00|
      103|                11.54|
      104|                25.00|

SQL Code Editor:


Contribute your code and comments through Disqus.

Previous: Order status report for each month for each company to find booked and cancelled order number and quantity.
Next: All People Report to the Given Manager.



SQL: Tips of the Day

How to select the nth row in a SQL database table?

Basically, PostgreSQL and MySQL supports the non-standard:

SELECT...
LIMIT y OFFSET x 

Oracle, DB2 and MSSQL supports the standard windowing functions:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

Database: SQL

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