Multiple SUM aggregates in ABAP CDS

I have a table of work orders with different status and multiple plants like below:

And I want to create a CDS to get the totals per plant: "total orders" plus a column for each status aggregate. The expected result is the following:

Since subqueries are not possible, the only solution I could think was a CDS for each aggregate. 1 for plant total and 1 for each plant status. And finally a top CDS with the expected result.

But this seems a lot of CDS for something that should be relatively simple, because in reality I have 7 different status and not only 3. Is there a better alternative to my solution?

Use CASE...WHEN...THEN...ELSE-expressions in your field list to convert the Status to multiple columns which contain either the integer 0 or the integer 1 depending on the value of Status:

define view VIEWNAME as select from DATABASE_TABLE {
    key OrderId,
    Plant,
    CASE Status WHEN 'OPEN' THEN 1 ELSE 0 END AS Open,
    CASE Status WHEN 'RELEASED' THEN 1 ELSE 0 END AS Released,
    CASE Status WHEN 'COMPLETED' THEN 1 ELSE 0 END AS Completed
}

Now you can create a second view which queries this first view. Make that view GROUP BY Plant. Then use COUNT( * ) to get the total number of orders per plant and SUM on the Open, Released and Completed fields to get their respective counts:

define view VIEWNAME2 as select from VIEWNAME {
    key Plant,
    COUNT( * ) as TotalOrders,
    SUM( Open ) AS Open,
    SUM( Released ) AS Released,
    SUM( Completed ) AS Completed
}
GROUP BY Plant