CODEWITHSUNDEEP
mysqlgroup-bysumdistinct
Landon
Landon

Reputation: 4108

Mysql Sum one row only once - with Group By

I have a seemingly simple problem that I can't seem to solve. Imagine an order table with a shipping value for that order. And imagine an item table that point to the order, and have a price:

Order

 |id |created   |shipping|
 |---|----------|--------|
 |101|2018-01-01|10      |
 |102|2018-01-01|10      |

Item

 |order_id|price   |
 |--------|--------|
 |101     |1       |
 |101     |2       |
 |102     |3       |

So there were two orders. Each had a shipping fee of $10. One order had two items, priced $1 and $2. The other had one item priced $3. Very simple so far.

I want to write a query, that will find the total shipping price and the total item price (summed) for every day (group by created). Naively, I would write this query:

SELECT 
    o.created, sum(o.shipping), sum(i.price)
FROM
    test_order o,
    test_item i
WHERE
    i.order_id = o.id
group by o.created

This query is very simple. It joins the two tables, and then group by that date, and sums vertically. The problem is with the shipping part. Since there are three rows after the join, there are three $10 shipping fees in the sum, which is entirely incorrect.

I could add a distinct to my shipping clause (sum(distinct o.shipping)), but that, too, is incorrect. It would evaluate those three 10s, and only keep one, resulting in a summed value of $10. It doesn't recognize that two of those 10s came from one source row, while the third 10 comes from an entirely different source row, and is desirable to include it in the sum.

My real query is much more complicated that this, but I distilled it down to this. I would think this is a common situation for many use cases. Of course, I could write a second query (or a sub query), but I'd like to not do that if at all possible.

Wanna play with this data yourself? Here is the sql:

CREATE TABLE IF NOT EXISTS `test_item` (
 `order_id` int(11) NOT NULL,
 `price` int(11) NOT NULL)
INSERT INTO `test_item` (`order_id`, `price`) VALUES (101, 1), (101, 2), (102, 3);

CREATE TABLE IF NOT EXISTS `test_order` (
 `id` int(12) NOT NULL,
 `created` datetime NOT NULL,
 `shipping` int(11) NOT NULL,
 PRIMARY KEY (`id`)
)
INSERT INTO `test_order` (`id`, `created`, `shipping`) VALUES (101, '2018-01-01 00:00:00', 10), (102, '2018-01-01 00:00:00', 10);

Thanks!

Upvotes: 3

Views: 2172

Answers (2)

stackFan
stackFan

Reputation: 1608

Using intermediate alias table would do the trick : 

select sumTable.createdDate, sum(sumTable.shippingSum),sum(sumTable.priceSum) from (
       SELECT 
       o.created, (o.shipping) as shippingSum, sum(i.price) as priceSum
       FROM
       test_order o,
       test_item i
       WHERE
       i.order_id = o.id
       group by o.created,o.id) 
as sumTable

I tested in SQL Fiddle over here resulting in sum of $30 and $6, which is probably what you wanted.

Upvotes: 0

T30
T30

Reputation: 12222

You are trying to avoid subqueries, but in this case I can't see other options:

SELECT created, sum(shipping), sum(price) from (
SELECT 
    o.created, o.shipping, sum(i.price) as price
FROM
    test_order o,
    test_item i
WHERE
    i.order_id = o.id
group by o.created, o.id) subquery;

Here the fiddle.

Upvotes: 1

Related Questions