w3resource
Run SQL Query

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.

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



Share this Tutorial / Exercise on : Facebook and Twitter

SQL: Tips of the Day

Can a foreign key be NULL and/or duplicate?

First remember a Foreign key simply requires that the value in that field must exist first in a different table (the parent table). That is all an Foreign key is by definition. Null by definition is not a value. Null means that we do not yet know what the value is.

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