Multi-table sum of cost breakdown by pricing category
Unfortunately, MySQL is lacking window or analytic functions which make complex grouping queries easier. For your use case, though, it should be possible to get it working with a single query. 

Doing a simple row-based group of your sample dataset:

MySQL [test]&gt; select orderId , category, count(*), sum(subtotal) from OrderLineItems group by 1,2;
+---------+-----------+----------+---------------+
| orderId | category  | count(*) | sum(subtotal) |
+---------+-----------+----------+---------------+
|  323607 | BUILD     |        5 |       2515.00 |
|  323607 | FINISHING |        1 |         30.00 |
|  323607 | SETUP     |        2 |         95.00 |
|  323614 | BUILD     |        4 |       4614.00 |
|  323614 | SETUP     |        1 |         36.00 |
|  323636 | BUILD     |        2 |        840.00 |
|  323636 | FINISHING |        1 |        130.00 |
|  323636 | SETUP     |        7 |       1152.00 |
|  323649 | BUILD     |        1 |        240.00 |
|  323649 | FINISHING |        1 |         30.00 |
|  323649 | SETUP     |        4 |       1995.00 |
+---------+-----------+----------+---------------+
11 rows in set (0.00 sec)


You can pivot the subtotals to columns using the trick of using an IF statement in the select. This way you get the main part of your data with only one query:

SELECT orderId , count(*), 
sum(if(category='BUILD', subtotal, 0)) as Build_Total,
sum(if(category='SETUP', subtotal, 0)) as Setup_Total,
sum(if(category='FINISHING', subtotal, 0)) as Finishing_Total,
sum(subtotal) as Total
from OrderLineItems
GROUP BY 1;


With output:

+---------+----------+-------------+-------------+-----------------+---------+
| orderId | count(*) | Build_Total | Setup_Total | Finishing_Total | Total   |
+---------+----------+-------------+-------------+-----------------+---------+
|  323607 |        8 |     2515.00 |       95.00 |           30.00 | 2640.00 |
|  323614 |        5 |     4614.00 |       36.00 |            0.00 | 4650.00 |
|  323636 |       10 |      840.00 |     1152.00 |          130.00 | 2122.00 |
|  323649 |        6 |      240.00 |     1995.00 |           30.00 | 2265.00 |
+---------+----------+-------------+-------------+-----------------+---------+
4 rows in set (0.00 sec)


You can use the same approach to get your date-based rollups.