scratch.sql

-- Works!
SELECT 
       name AS "Name", 
       codes AS "Product Codes",
       COALESCE(total, 0.00) AS "Total Sales (in Dollars)"
FROM
(SELECT
        meta.id AS meta_id,
        meta.name AS "name",
        string_agg(DISTINCT product.code, ', ') AS "codes"
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
WHERE
     product.is_active = True
GROUP BY meta.id) t1
LEFT JOIN
(SELECT meta.id AS meta_id,
        COALESCE(SUM(line.quantity_ordered *
                      line.unit_price_at_checkout_time), 
                  0) AS "total"
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 
WHERE
        (orders.order_status >= 3
         AND
         orders.order_date BETWEEN '2013-04-01' AND '2013-04-02')
GROUP BY meta.id) t2
ON t2.meta_id = t1.meta_id
ORDER BY COALESCE(total, 0.00);