mysql multi group sum <p>Below is my payments mysql 5.7.9 table need hlpe to write a query</p> <p>payments</p> <pre><code>|s.no |transaction |order-id |order-item-code|amount-type |amount ------------------------------------------------------------------------------------- |1 |Order |1 |11 |ItemPrice |200 |2 |Order |1 |11 |ItemPrice |100 |3 |Order |1 |11 |ItemFees |-12 |4 |Order |1 |11 |ItemFees |-1.74 |5 |Order |1 |11 |ItemFees |-10 |6 |Order |1 |11 |ItemFees |-1.45 |7 |Order |1 |11 |ItemFees |-4 |8 |Order |1 |11 |ItemFees |-0.58 |9 |Order |1 |22 |ItemPrice |150 |10 |Order |1 |22 |ItemPrice |50 |11 |Order |1 |22 |ItemFees |-12 |12 |Order |1 |22 |ItemFees |-1.74 |13 |Order |1 |22 |ItemFees |-10 |14 |Order |1 |22 |ItemFees |-1.45 |15 |Order |1 |22 |ItemFees |-4 |16 |Order |1 |22 |ItemFees |-0.58 |17 |Ship |1 | |other-transaction|-55 |18 |Ship Tax |1 | |other-transaction|-7.98 |19 |Order |2 |33 |ItemPrice |450 |20 |Order |2 |33 |ItemPrice |150 |21 |Order |2 |33 |ItemFees |-36 |22 |Order |2 |33 |ItemFees |-5.22 |23 |Order |2 |33 |ItemFees |-30 |24 |Order |2 |33 |ItemFees |-4.35 |25 |Order |2 |33 |ItemFees |-12 |26 |Order |2 |33 |ItemFees |-1.74 |27 |Ship |2 | |other-transaction|-55 |28 |Ship Tax |2 | |other-transaction|-7.98 </code></pre> <p>Expected result</p> <pre><code> |order-id |order-item-code |Received --------------------------------------------- |1 |11 |238.74 |1 |22 |138.74 |2 |33 |447.71 </code></pre> <p>Calculation Logic:</p> <p>sum (itemprice per order-item-code) + sum(itemfees per order-item-code) + sum (other-transcation per order-id) / distinct of order-item-id in order-id</p> <p>other transcation is the shipping fee charged on a order, I need to divide the sum of other transcations with no of unique items present in the order. in case of order-id- 1, we have 2 items as only two item id code, in case of order-id -2 we have one item. it is the no of distinct order-item -code present</p>