MySQL SUM of one column, DISTINCT of ID column
<p>I'm trying to create a summary report of our orders but having trouble extracting all my required data in a single query.</p>

<p>The data I'd like to extract:</p>

<ul>
<li>subtotal - SUM of all sale prices</li>
<li>delivery total - SUM of all orders deliveryTotal</li>
<li>orders - COUNT of DISTINCT orderIds</li>
<li>quantity - SUM of all quantity ordered</li>
</ul>

<p>Orders table (simplified for this example)</p>

<pre><code>| orderId | deliveryTotal | total |
|---------|---------------|-------|
| 1       | 5             | 15    |
| 2       | 5             | 15    |
| 3       | 7.50          | 27.50 |
</code></pre>

<p>Order items table</p>

<pre><code>| orderItemId | orderId | productId | salePrice | quantity |
|-------------|---------|-----------|-----------|----------|
| 1           | 1       | 1         | 10        | 1        |
| 2           | 2       | 1         | 10        | 1        |
| 3           | 3       | 1         | 10        | 1        |
| 4           | 3       | 2         | 10        | 1        |
</code></pre>

<p>My current query for extracting this data is</p>

<pre><code>SELECT
    SUM(i.salePrice * i.quantity) as subtotal,
    SUM(DISTINCT o.deliveryTotal) as deliveryTotal,
    COUNT(DISTINCT o.orderId) as orders,
    SUM(i.quantity) as quantity
FROM orderItems i
INNER JOIN orders o ON o.orderId = i.orderId
</code></pre>

<p>Which results in a correct subtotal, order count and quantity sum. But delivery total is returned as 12.50 when I'm after 17.50. If I do <code>SUM(o.deliveryTotal)</code> it will return 25.</p>

<p>EDIT: Desired results</p>

<pre><code>| subtotal | deliveryTotal | orders | quantity |
|----------|---------------|--------|----------|
| 40.00    | 17.50         | 3      | 4        |
</code></pre>