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

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

FieldTypeNullKeyDefaultExtra
product_namevarchar(25)YES
unit_typevarchar(5)YES
sale_datedateYES
sale_qtyint(11)YES

Data:

product_nameunit_typesale_datesale_qty
MunchosP2018-05-1520
Boyer ChocolateP2018-04-2730
CocaColaL2018-04-1025
Fruit CakesP2018-07-1230
CocaColaL2018-07-0750
FantaL2018-01-2770
Chex MixP2018-09-1740
Jaffa CakesP2018-06-2540
Pom-BearP 2018-02-1130
Twix ChocolateP2018-12-2450
LimcaL2018-03-1550
MirindaL2018-02-0540

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.

﻿

Follow us on Facebook and Twitter for latest update.