im trying to display all the items sold and not sold within a period of time
im unable to display on the join tables all the items that have previously arrived if there were none sold.
select arrivals.description,
COALESCE(sum(orders.quantity ), 0) as quantity
from arrivals
left join
orders
on arrivals.description = orders.description
where orders.date between '2022-11-01' and '2022-12-20'
group by orders.description`ARRIVALS
the problem is that by referencig orders.date doesnt display descriptions where nothing was sold within that period, it works if i use arrivals.date but i want to display the dates from orders.
ORDERS
| id|date | description | quantity |
|---|----------|-------------------|------------|
|7| 2022-11-27 | nike 500 black 70 |1|
|1| 2022-11-24 | nike 500 black 70 |1|
|2| 2022-11-24 | nike 500 black 60 |1|
|6| 2022-11-28 | adidas 1000 white 90 |1|
|5| 2022-11-27 | adidas 1000 white 90 |1|
|4| 2022-10-31 | adidas 1000 white 90 |1|
|3| 2022-10-31 | adidas 1000 white 80 |1|
--------------------------------------------------------
ARRIVALS
| id |date | description | quantity |
|----|------------|----------------------|-------------|
|1 |2022-10-30 | nike 500 black 50 | 2 |
|2 | 2022-10-30 | nike 500 black 60 | 3 |
|3 | 2022-10-30 | nike 500 black 70 | 4 |
|4 | 2022-10-29 | adidas 1000 white 80 | 2 |
|5 | 2022-10-29 | adidas 1000 white 90 | 3 |
|6 | 2022-10-29 | adidas 1000 white 110| 2 |
i only get this result:
| description | quantity |
|———————-|————-|
| nike 500 black 60 |1 |
| nike 500 black 70 |2 |
| adidas 1000 white 90 |2 |
>Solution :
Can you try this?
select arrivals.description,
COALESCE(sum(orders.quantity ), 0) as quantity
from arrivals
left join orders on arrivals.description = orders.description
and orders.date between '2022-11-01' and '2022-12-20'
group by arrivals.description
This will return all the descriptions in the arrivals table, with a quantity of 0 for the ones that have no matching records in the orders table within the specified date range.