SQL Challenges-1: Show running quantiry for each unit type of item

SQL Challenges-1: Exercise-48 with Solution

From the following table write a SQL query to find the total sale quantity of items of each unit type at each day. Return unit type, date and total sale quantity at each day. Order the result table by gender and day.

Input:

Table: sale

Structure:

Data:

Sample Solution:

SQL Code(MySQL):

Create table  sale (product_name varchar(25),unit_type varchar(5),sale_date date,sale_qty integer);

insert into sale  values ('Munchos','P','2018-05-15',20);
insert into sale  values ('Boyer Chocolate','P','2018-04-27', 30);
insert into sale  values ('CocaCola','L','2018-04-10', 25);
insert into sale  values ('Fruit Cakes','P','2018-07-12', 30);
insert into sale  values ('CocaCola','L','2018-07-07', 50);
insert into sale  values ('Fanta','L','2018-01-27', 70);
insert into sale  values ('Chex Mix','P','2018-09-17', 40);
insert into sale  values ('Jaffa Cakes','P','2018-06-25', 40);
insert into sale  values ('Pom-Bear','P','2018-02-11', 30);
insert into sale  values ('Twix Chocolate','P','2018-12-24', 50);
insert into sale  values ('Limca','L','2018-03-15', 50);
insert into sale  values ('Mirinda','L','2018-02-05', 40);



select
    s1.unit_type,s1.sale_date,
    sum(s2.sale_qty) as "running unit"
from sale as s1
join sale as s2
on s1.sale_date >= s2.sale_date and s1.unit_type = s2.unit_type
group by s1.unit_type,s1.sale_date
order by unit_type,sale_date;

Sample Output:

unit_type|sale_date |running unit|
---------|----------|------------|
L        |2018-01-27|          70|
L        |2018-02-05|         110|
L        |2018-03-15|         160|
L        |2018-04-10|         185|
L        |2018-07-07|         235|
P        |2018-02-11|          30|
P        |2018-04-27|          60|
P        |2018-05-15|          80|
P        |2018-06-25|         120|
P        |2018-07-12|         150|
P        |2018-09-17|         190|
P        |2018-12-24|         240|

SQL Code Editor:

Contribute your code and comments through Disqus.

Previous: Find the Team Size.
Next: List the items sold out within a specific period.