How to check the existence of related records in group by query?

Question

There are 3 simple tables Orders, OrderLines and OrderLineRealizations with an obvious relationships. I want to return some information about orders with an additional column that represents existence of a realization of any line for given group.

Simplified query looks like this:

SELECT 
Orders.Id,
SUM(OrderLines.Quantity) AS TotalAmount,
-- Is there a realization?
FROM Orders
INNER JOIN OrderLines ON OrderId = Orders.Id
GROUP BY Orders.Id -- some additional grouping

I tried:

SELECT 
Orders.Id,
SUM(OrderLines.Quantity) AS TotalAmount,
CASE
    WHEN EXISTS (SELECT * FROM OrderLineRealizations
                 WHERE OrderLineId IN (OrderLines.Id))
    THEN 1
    ELSE 0
END AS RealizationExists
FROM Orders
INNER JOIN OrderLines ON OrderId = Orders.Id
GROUP BY Orders.Id

But it's not allowed to use IN like this. I also cannot use Left Join for realizations, because then I get an incorrect TotalAmount.

Result I want to achieve:

Orders
- - - -
Id
1
2

OrderLines
- - - -
Id | OrderId | Quantity
1  | 1       | 10 
2  | 1       | 15
3  | 2       | 11

OrderLineRealizations
- - - -
Id | OrderLineId |
1  | 1           |

Result
- - - -
OrderId | TotalAmount | RealizationExists
1       | 25          | 1
2       | 11          | 0

What can I write a query to get an expected result?

Answer 1

SELECT 
  Id,
  SUM(Quantity) AS TotalAmount,
  max(RealizationExists) as RealizationExists
from ( 
    SELECT 
      Orders.Id,
      OrderLines.Quantity, 
      CASE
        WHEN EXISTS (SELECT * FROM OrderLineRealizations olr 
                     where olr.OrderLineId = OrderLines.Id)
        THEN 1
        ELSE 0
        END as RealizationExists
    FROM Orders
    INNER JOIN OrderLines ON OrderId = Orders.Id
) tmp
group by id;

Here is DBFiddle demo