Q:

Adding sub queried column to complex select query

I have the following query:
(SELECT phppos_sales.sale_id as sale_id, (item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)+(item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)*(SUM(CASE WHEN cumulative != 1 THEN percent ELSE 0 END)/100) +(((item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)*(SUM(CASE WHEN cumulative != 1 THEN percent ELSE 0 END)/100) + (item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)) *(SUM(CASE WHEN cumulative = 1 THEN percent ELSE 0 END))/100) as total 

FROM phppos_sales_items
INNER JOIN phppos_sales ON phppos_sales_items.sale_id=phppos_sales.sale_id 
INNER JOIN phppos_items ON phppos_sales_items.item_id=phppos_items.item_id 
LEFT OUTER JOIN phppos_sales_items_taxes ON phppos_sales_items.sale_id=phppos_sales_items_taxes.sale_id and phppos_sales_items.item_id=phppos_sales_items_taxes.item_id and phppos_sales_items.line=phppos_sales_items_taxes.line 

WHERE sale_time BETWEEN "2014-08-01 00:00:00" and "2014-08-09 23:59:59" 

GROUP BY phppos_sales.sale_id, phppos_sales_items.item_id, phppos_sales_items.line) 

Example Result:
| sale_id | total                              |
+---------+------------------------------------+
|    3863 | 172.778400000000000000000000000000 |
|    3863 | 362.815200000000000000000000000000 |
|    3863 | 129.578400000000000000000000000000 |
|    3863 | 215.973000000000000000000000000000 |
|    3863 | 615.519000000000000000000000000000 |
+---------+------------------------------------+

I want to add a third column payment_amount for each sale. To find the payment amount the following query is executed.
SELECT SUM(payment_amount) from phppos_sales_payments WHERE sale_id = XXX;

Is there a way to do this in 1 query?

A:

to your query just add this.
in the SELECT part add temp.summed_amount
to the JOIN's add
LEFT JOIN
(   SELECT
        SUM(payment_amount) as summed_amount,
        sale_id 
    FROM phppos_sales_payments 
) temp on temp.sale_id = phppos_sales.sale_id;

NOTE
you should try to format your queries as it's basically impossible to read currently
so your query should look something on the lines of this.
SELECT 
    phppos_sales.sale_id as sale_id, 
    (   item_unit_price * quantity_purchased-item_unit_price * quantity_purchased * discount_percent / 100
    ) +
    (   item_unit_price * quantity_purchased-item_unit_price * quantity_purchased * discount_percent / 100
    ) *
    (   SUM
        (   CASE WHEN cumulative != 1 THEN percent ELSE 0 END
        ) / 100
    ) +
    (
        (
            (   item_unit_price * quantity_purchased-item_unit_price * quantity_purchased * discount_percent / 100
            ) *
            (   SUM
                (CASE WHEN cumulative != 1 THEN percent ELSE 0 END) / 100
                ) + 
                (   item_unit_price * quantity_purchased-item_unit_price * quantity_purchased * discount_percent / 100
                )
            ) *
            (   SUM
                (CASE WHEN cumulative = 1 THEN percent ELSE 0 END)
            ) / 100
        ) as total, 
    temp.summed_amount
FROM phppos_sales_items
INNER JOIN phppos_sales 
    ON phppos_sales_items.sale_id=phppos_sales.sale_id 
INNER JOIN phppos_items 
    ON phppos_sales_items.item_id=phppos_items.item_id 
LEFT OUTER JOIN phppos_sales_items_taxes 
    ON phppos_sales_items.sale_id=phppos_sales_items_taxes.sale_id 
    AND phppos_sales_items.item_id=phppos_sales_items_taxes.item_id 
    AND phppos_sales_items.line=phppos_sales_items_taxes.line 
LEFT JOIN
    (   SELECT
            SUM(payment_amount) as summed_amount,
            sale_id 
        FROM phppos_sales_payments 
    ) temp on temp.sale_id = phppos_sales.sale_id;
WHERE sale_time BETWEEN "2014-08-01 00:00:00" AND "2014-08-09 23:59:59" 
GROUP BY 
    phppos_sales.sale_id, 
    phppos_sales_items.item_id, 
    phppos_sales_items.line 

