Max13 Max13 - 13 days ago 10
MySQL Question

Mysql 2 SUM and different group by

I need 2 different

SUM
, but not grouped by the same set.

The question is a bit odd, let's take an example. I have a query (let's say a classic select) which outputs this set of data:

+----+------+------+----+----------+
| id | t_id | o_id | lt | quantity |
+----+------+------+----+----------+
| 1 | 1 | 1 | 30 | 50 |
| 2 | 2 | 10 | 90 | 90 |
| 3 | 4 | 10 | 10 | 20 |
| 4 | 4 | 10 |  5 | 20 |
| 5 | 4 | 9 | 20 | 20 |
+----+------+------+----+----------+


Now for a brief explanation,
t_id
is a
fuel id
,
o_id
is an
order id
,
lt
is the amount of litres shipped, and
quantity
is the ordered quantity (in litres) for a given fuel.

So, if I explain line 1: The
order #1
is for 50 litres of
fuel #1
, for now only 30 litres have been shipped.

Line 2, 3 and 4: The
order #10
needs 90 litres of
fuel #2
AND 20 litres of
fuel #4
. 90 litres of
fuel #2
have been shipped, and 10+5 litres of fuel
#4
have been shipped. In other words:
order #10
needs 90+20 litres of fuel (any fuel), and 90+10+5 litres have been shipped.

Now, the tricky part is that I would like to
SELECT SUM(lt), SUM(quantity)
,
lt
grouped by
o_id
but
quantity
grouped by
t_id, o_id
. The expected output is:

+----+------+------+---------+---------------+
| id | t_id | o_id | SUM(lt) | SUM(quantity) |
+----+------+------+---------+---------------+
| 1 | 1 | 1 |  30 | 50 |
| 2 | 2 | 10 |  105 | 110 | <-- 90+10+5 | 90+20
| 5 | 4 | 9 |  20 | 20 |
+----+------+------+---------+---------------+


Any idea on how to achieve that? I hope my issue is well explained.

Thanks for your help.

PS: In fact, the whole question can be summarized by: Is it possible to have 2 aggregates grouped-by different values?

Answer

This answer might be different if you were working from the original source rows, but if we assume that you're going to take the pasted table as a source, then the following query should get you the correct result:

Select a.o_id, 
    a.lt,
    b.quantity
  From (Select o_id, Sum(lt) As lt From Input As i Group By o_id) As a
  Join (
      Select o_id, Sum(quantity) As quantity
        From (
            Select o_id, t_id, Min(quantity) As quantity 
              From Input Group By o_id, t_id
             ) As c
        Group By o_id
       ) As b
    On a.o_id = b.o_id;

In the supplied input data, it looks as if the quantity field is repeated for all rows with the same t_id and o_id so I used a subquery to collapse all those rows to one and just get a single (Min()) value for quantity (subquery c). I then aggregated that by o_id in an outer subquery (b). Values for lt were summed across o_id in the third subquery a.