I have multiple currency based orders in database. When I was trying select order statistics, MySQL SUM not calculating same values.
For example I have same prices on two orders 2550 but sum calculating only one order and if I'll change one order price to 2551 it's working correctly.
$this->db->select('
SUM(distinct(IF(EUR.amount>0 AND EUR.stat="1",(EUR.amount),"0"))) as EUR,
SUM(distinct(IF(EUR.stat="3",(EUR.amount),"0"))) EURPriceAdd,
SUM(distinct(IF(EUR.amount>0 AND EUR.stat="1",(EUR.amount),"0")-IF(EUR.stat="2",(EUR.amount),"0")+IF(EUR.stat="3",(EUR.amount),"0"))) as EURProfit,
SUM(distinct(IF(USD.amount>0 AND USD.stat="1",(USD.amount),"0"))) as USD,
SUM(distinct(IF(USD.stat="3",(USD.amount),"0"))) USDPriceAdd,
SUM(distinct(IF(USD.amount>0 AND USD.stat="1",(USD.amount),"0")-IF(USD.stat="2",(USD.amount),"0")+IF(USD.stat="3",(USD.amount),"0"))) as USDProfit,,
', false);
$this->db->join('orders_prices AS EUR', 'EUR.currency = 2 AND EUR.order_id = orders.id', 'LEFT');
$this->db->join('orders_prices AS USD', 'USD.currency = 1 AND USD.order_id = orders.id', 'LEFT');
$this->db->join('orders_address', 'orders_address.order_id = orders.id');
$this->db->join('exchange_rate', 'MONTH(exchange_rate.date) = MONTH(orders.converted_at) AND YEAR(exchange_rate.date) = YEAR(orders.converted_at)', 'INNER');
$this->db->group_by('orders_address.country');
$directionIncomes = $this->db->get('orders')->result();
I found the main problem. Wrong results caused by joining of "exchange_rates" table. When I'm trying join exchange rates database returns much more result than he actually is.
This is SQL query:
SELECT SUM(IF(USD.amount>0 AND USD.stat="1", (USD.amount), "0")) as USD, SUM(IF(USD.stat="3", (USD.amount), "0")) USDPriceAdd, SUM(IF(USD.amount>0 AND USD.stat="1", (USD.amount), "0")-IF(USD.stat="2", (USD.amount), "0")+IF(USD.stat="3", (USD.amount), "0")) as USDProfit
FROM `orders`
LEFT JOIN `orders_prices` AS `USD` ON `USD`.`currency` = 1 AND `USD`.`order_id` = `orders`.`id`
LEFT JOIN `orders_address` ON `orders_address`.`order_id` = `orders`.`id`
LEFT JOIN `exchange_rate` ON `exchange_rate`.`currency`="1"
WHERE `orders`.`pay_status` = 3
AND `orders`.`status` > 1
AND `orders`.`status` < 8
AND `orders_address`.`country` = 'BY'
AND DATE(orders.converted_at) >= '2021-01-01'
AND DATE(orders.converted_at) <= '2021-06-15'
GROUP BY `orders_address`.`country`
Data structure of exchange_rates
You are doing SUM(distinct(IF(EUR.amount>0 ... which is summing only distinct values (I guess, I didn't know there was such an option). Use SUM(IF(EUR.amount>0 ... instead
