Max13 Max13 - 10 months ago 60
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 Source

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.