SQL Challenges-1: Show running quantiry for each unit type of item
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:
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
product_name | varchar(25) | YES | |||
unit_type | varchar(5) | YES | |||
sale_date | date | YES | |||
sale_qty | int(11) | YES |
Data:
product_name | unit_type | sale_date | sale_qty |
---|---|---|---|
Munchos | P | 2018-05-15 | 20 |
Boyer Chocolate | P | 2018-04-27 | 30 |
CocaCola | L | 2018-04-10 | 25 |
Fruit Cakes | P | 2018-07-12 | 30 |
CocaCola | L | 2018-07-07 | 50 |
Fanta | L | 2018-01-27 | 70 |
Chex Mix | P | 2018-09-17 | 40 |
Jaffa Cakes | P | 2018-06-25 | 40 |
Pom-Bear | P | 2018-02-11 | 30 |
Twix Chocolate | P | 2018-12-24 | 50 |
Limca | L | 2018-03-15 | 50 |
Mirinda | L | 2018-02-05 | 40 |
Sample Solution:
SQL Code(MySQL):
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.