By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Create Table Stores(Id INT, Name VARCHAR(50), Type VARCHAR(50));
Insert Into Stores Values(1,'Store 1','Weekly'),
(2,'Store 2','Yearly'),
(3,'Store 3','Weekly'),
(4,'Store 4','Weekly');
Create Table Orders(Id INT, StoreId INT, OrderDate DATE, Qty INT);
Insert Into Orders Values(1,1,'2022-01-31',2),
(2,1,'2022-12-31',5),
(3,2,'2022-01-28',30),
(4,2,'2022-06-30',50),
(5,2,'2022-12-31',70),
(6,3,'2022-06-15',8),
(7,3,'2022-12-27',9),
(8,3,'2022-12-31',3);
Records: 4 Duplicates: 0 Warnings: 0
Records: 8 Duplicates: 0 Warnings: 0
SELECT s.Id, s.Name,
COUNT(Case When s.Type = 'Yearly' Then o.Id
Else Case When OrderDate>='2022-12-26' AND OrderDate<='2023-01-01' Then o.Id End
End) As 'Count of orders',
Sum(Case When s.Type = 'Yearly' Then o.Qty
Else Case When OrderDate>='2022-12-26' AND OrderDate<='2023-01-01' Then o.Qty Else 0 End
End) AS 'Total Qty'
FROM Stores AS s
LEFT JOIN Orders as o ON o.StoreId = s.id
GROUP BY s.Id, s.Name
ORDER BY Max(OrderDate) DESC
| Id | Name | Count of orders | Total Qty |
|---|---|---|---|
| 1 | Store 1 | 1 | 5 |
| 2 | Store 2 | 3 | 150 |
| 3 | Store 3 | 2 | 12 |
| 4 | Store 4 | 0 | 0 |