scratch.sql

SELECT
        meta.name AS "Name",
        string_agg(DISTINCT product.code, ', ') AS "Product Codes",
        COALESCE(SUM(line.quantity_ordered *
                     line.unit_price_at_checkout_time), 
                 0) AS "Total Quantity Ordered (in dollars)"
FROM warehouse_pieces_piecemeta AS meta
LEFT JOIN warehouse_pieces_piece AS piece
     ON piece.piece_meta_id = meta.id
LEFT JOIN products_product AS product
     ON product.piece_id = piece.id
LEFT JOIN orders_orderline AS line
     ON line.product_id = product.id
LEFT JOIN orders_order AS orders
     ON orders.id = line.order_id AND orders.order_date=COALESCE(orders.order_date, '2010-01-01')
WHERE
     product.is_active = True
     AND ((
         orders.order_status >= 3  -- ORDER_STATUS_PLACED = 3
         AND
         orders.order_date BETWEEN '2013-04-01' AND '2013-04-02'
         )
         OR
         orders.order_date IS NULL
         )
GROUP BY meta.id
ORDER BY COALESCE(
                  SUM(line.quantity_ordered *
                      line.unit_price_at_checkout_time),
                  0)
;