By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE OrderItems(
[ID] [int] NOT NULL,
[OrderID] [int] NOT NULL,
[ItemTypeID] [int] NOT NULL
)
CREATE TABLE Orders(
[ID] [int] NOT NULL,
[OrderDate] [date] NOT NULL
)
INSERT [OrderItems] ([ID], [OrderID], [ItemTypeID]) VALUES
(1, 1, 1000)
, (2, 1, 6000)
, (3, 2, 1000)
, (4, 3, 1000)
, (5, 3, 1000)
, (6, 4, 1000)
, (7, 4, 6000)
, (8, 5, 6000)
, (9, 6, 4000)
, (10, 7, 4000)
, (11, 8, 3000)
, (12, 9, 6000)
, (13, 10, 6000)
, (14, 11, 6000);
INSERT [Orders] ([ID], [OrderDate]) VALUES
(1, CAST(N'2021-12-01' AS Date))
, (2, CAST(N'2021-12-01' AS Date))
, (3, CAST(N'2021-12-02' AS Date))
, (4, CAST(N'2021-12-03' AS Date))
, (5, CAST(N'2021-12-03' AS Date))
, (6, CAST(N'2021-12-03' AS Date))
, (7, CAST(N'2021-12-03' AS Date))
, (8, CAST(N'2021-12-03' AS Date))
, (9, CAST(N'2021-12-03' AS Date))
, (10, CAST(N'2021-12-03' AS Date))
, (11, CAST(N'2021-12-03' AS Date));
25 rows affected
SELECT O.OrderDate, count(Z.hasDate) OrdersWithItem, count(*)-count(Z.HasDate) as OrdersWithoutItems
FROM Orders O
OUTER APPLY (SELECT TOP 1 1 as hasDate
FROM OrderItems OI
WHERE OI.ItemTypeID=6000
AND O.ID = OI.OrderID
ORDER BY OI.ID ) z
GROUP BY O.OrderDate
| OrderDate | OrdersWithItem | OrdersWithoutItems |
|---|---|---|
| 2021-12-01 | 1 | 1 |
| 2021-12-02 | 0 | 1 |
| 2021-12-03 | 5 | 3 |
Warning: Null value is eliminated by an aggregate or other SET operation.