Is there a way to sum a product of two columns in MySQL?
<p>Here are my tables:</p>

<pre><code>-- receipts
| id | date       |
|----|------------|
| 1  | 2016-12-20 |
| 2  | 2016-12-20 |
| 3  | 2016-12-21 |

-- purchases
| id | receipt_id | item        | price | tax_rate |
|----|------------|-------------|-------|----------|
| 1  | 1          | apples      |  3.89 | 0.000    |
| 2  | 1          | canned soup |  2.99 | 0.095    |
| 3  | 1          | candy bar   |  0.99 | 0.095    |
| 4  | 2          | gas         | 26.78 | 0.000    |
| 5  | 3          | chips       |  3.99 | 0.095    |
| 6  | 3          | dip         |  2.99 | 0.095    |
</code></pre>

<p>I am trying to do a query that returns the 3 items from the receipts table with the subtotal, the total tax, and the grand total. This is what I have for the SQL so far.</p>

<pre><code>SELECT 
    receipts.id, 
    receipts.date, 
    SUM(purchases.price) AS subtotal, 
    SUM(purchases.price * purchases.tax_rate) AS tax,
    SUM(subtotal + tax_rate)
FROM receipts
RIGHT JOIN purchases ON purchases.receipt_id = receipts.id;
</code></pre>

<p>The end result should look like this:</p>

<pre><code>| id | date       | subtotal | tax  | total |
|----|------------|----------|------|-------|
| 1  | 2016-12-20 |  7.87    | 0.38 |  8.25 |
| 2  | 2016-12-20 | 26.78    | 0.00 | 26.78 |
| 3  | 2016-12-21 |  6.98    | 0.66 |  7.64 |
</code></pre>