sales table:
| date | item_no | store_group | sales_qty |
|---|---|---|---|
| 1/1/2022 | 123 | A | 1234 |
| 1/1/2022 | 123 | B | 7246 |
| 1/1/2022 | 123 | C | 6516 |
inventory table:
| date | item_no | stock |
|---|---|---|
| 1/1/2022 | 123 | 10000 |
A typical join on date and item_no would result in the stock of 10,000 repeated in the 3 rows. How do I make it look like
CodePudding user response：
Try this. If you select date , item and stock in data studio you will see single line like of table inventory data. The moment you include, storeGroup or salesQty there will be multiple line like of table sale.
select s.date, s.item_no, s.store_group, sum(s.sales_qty) sales_qty, max(i.stock) stock
from sales s
left join inventory i on s.date = i.date and s.item_no = i.item_no
group by s.date, s.item_no, s.store_group