SQL query to get minimal sum value and associated column from table

I have following tables:

t1 – Items

| Id | Item   |
|  1 | I1     |
|  2 | I2     |

t2 – Item_elements

| Id |  Item_id | Element    | Our_quantity  |  Client_quantity |
|  1 |       1  | E11        |          100  |               0  |
|  2 |       1  | E12        |           20  |             300  |
|  3 |       2  | E21        |          300  |             100  |
|  4 |       2  | E22        |            5  |             300  |

t3 – Element_operations

| Id |  Element_id | Operations_number |
|  1 |           1 |               100 |
|  2 |           1 |                50 |
|  3 |           2 |                50 |
|  4 |           2 |                50 |
|  5 |           3 |                50 |
|  6 |           3 |                50 |
|  7 |           3 |                50 |
|  8 |           3 |                50 |
|  9 |           4 |                10 |

I need SQL query which return table witch items (t1) AND element row associated with item table which has minimum operations number

Desired Output: Table should look like

| Id|Item| Id_el |Our_quantity| Client_quantity | Count Operations_number|
|  1| I1 |     2 |         20 |             300 |                    100 |
|  2| I2 |     4 |          5 |             300 |                     10 |

result

I tried that query

SELECT t2.Id,Item_id,Our_Quantity,Client_Quantity,SUM(Operations_number) 
FROM t2 LEFT JOIN t3 ON t2.Id=t3.Id_el GROUP BY t2.Id)

​x
 
SELECT t2.Id,Item_id,Our_Quantity,Client_Quantity,SUM(Operations_number) FROM t2 LEFT JOIN t3 ON t2.Id=t3.Id_el GROUP BY t2.Id)​

Tried Result:

| Id |  Item_id | Our_quantity  |  Client_quantity |SUM(Operations_number)
|  1 |       1  |          100  |               0  |                   150
|  2 |       1  |           20  |             300  |                   100
|  3 |       2  |          300  |             100  |                   200
|  4 |       2  |            5  |             300  |                    10

What should I do next?

Now I have 2 table:

| Element Id |  Item_id |Sum operations_number for element  |
|  1         |       1  |                          150      |
|  2         |       1  |                          100      |
|  3         |       2  |                          200      |
|  4         |       2  |                           10      |

| Item Id |     Item  |
|  1      |       I1  |
|  2      |       I2  |

How can I join them to get this table?

Item Element who has minimal sum operations number.

| Item Id |     Item  | Element_Id | Sum operations_number for element |
|  1      |       I1  | 2          |                          100      |
|  2      |       I2  | 4          |                           10      |

Answer

You could get desired output using this..

SELECT 
  t.*,
  MIN(t.opsum) AS `Count Operations_number` 
FROM
  (SELECT 
    a.*,
    b.Id AS `Id_el`,
    b.`Our_quantity`,
    b.`Client_quantity`,
    SUM(c.`Operations_number`) AS opsum 
  FROM
    `t1` AS a 
    LEFT JOIN `t2` AS b 
      ON a.`Id` = b.`Item_id` 
    LEFT JOIN `t3` AS c 
      ON b.`Id` = c.`Element_id` 
  GROUP BY a.Id,
    b.Id 
  ORDER BY a.`Id` ASC,
    opsum ASC) AS t 
GROUP BY t.Id ;

 
SELECT   t.*,  MIN(t.opsum) AS `Count Operations_number` FROM  (SELECT     a.*,    b.Id AS `Id_el`,    b.`Our_quantity`,    b.`Client_quantity`,    SUM(c.`Operations_number`) AS opsum   FROM    `t1` AS a     LEFT JOIN `t2` AS b       ON a.`Id` = b.`Item_id`     LEFT JOIN `t3` AS c       ON b.`Id` = c.`Element_id`   GROUP BY a.Id,    b.Id   ORDER BY a.`Id` ASC,    opsum ASC) AS t GROUP BY t.Id ;​

FIDDLE HERE

Advertisement

Answer