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:
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):
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.
SQL: Tips of the Day
SQL Server SELECT into existing table.
INSERT INTO dbo.TABLETWO SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:
INSERT INTO dbo.TABLETWO (col1, col2) SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
Database: SQL Server
Ref: https://bit.ly/3y6tpA3
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework