USE tpch1; drop table if exists `shipamounts`; drop table if exists `ship1`; drop table if exists `ship2`; CREATE TABLE `shipamounts` ( `OrderNum` varchar(50) DEFAULT NULL, `OrderLine` int(11) DEFAULT NULL, `ShippingAmount` decimal(10,2) DEFAULT NULL, `ShippingDiscountAmount` decimal(10,2) DEFAULT NULL, `ShippingOrderAmount` decimal(10,2) DEFAULT NULL, `ShippingDiscountOrderAmount` decimal(10,2) DEFAULT NULL ) engine=columnstore DEFAULT CHARSET=latin1; CREATE TABLE `ship1` ( `OrderNum` varchar(50) DEFAULT NULL, `OrderLine` int(11) DEFAULT NULL, `ShippingAmount` decimal(10,2) DEFAULT NULL, `ShippingOrderAmount` decimal(10,2) DEFAULT NULL, `ShippingDiscountAmount` decimal(10,2) DEFAULT NULL, `ShippingDiscountOrderAmount` decimal(10,2) DEFAULT NULL ) engine=columnstore DEFAULT CHARSET=latin1; CREATE TABLE `ship2` ( `OrderNum` varchar(50) DEFAULT NULL, `OrderLine` int(11) DEFAULT NULL, `ShippingAmount` decimal(10,2) DEFAULT NULL, `ShippingOrderAmount` decimal(10,2) DEFAULT NULL, `ShippingDiscountAmount` decimal(10,2) DEFAULT NULL, `ShippingDiscountOrderAmount` decimal(10,2) DEFAULT NULL ) engine=columnstore DEFAULT CHARSET=latin1; insert into shipamounts values (2357203684, 352338, 0, 0, 0, 0); insert into ship1 values (2357203684, 352338, 0, 0, 0, 0); insert into ship2 values (2357203684, 352338, 0, 0, 0, 0); SELECT s.OrderNum, s.OrderLine, s.ShippingAmount, s.ShippingOrderAmount, total.ShippingTotalAmount, s.ShippingDiscountAmount, s.ShippingDiscountOrderAmount, total.ShippingDiscountTotalAmount, IF( s.OrderLine = MIN(t.OrderLine), 1, 0 ) FROM shipAmounts AS s LEFT OUTER JOIN ship1 AS t ON s.OrderNum = t.OrderNum LEFT OUTER JOIN (SELECT OrderNum, SUM(ShippingAmount) AS ShippingTotalAmount, SUM(ShippingDiscountAmount) AS ShippingDiscountTotalAmount FROM ship2 GROUP BY OrderNum) AS total ON s.OrderNum = total.OrderNum GROUP BY s.OrderNum,s.OrderLine,3,4,5,6,7,8; OrderNum OrderLine ShippingAmount ShippingOrderAmount ShippingTotalAmount ShippingDiscountAmount ShippingDiscountOrderAmount ShippingDiscountTotalAmount IF( s.OrderLine = MIN(t.OrderLine), 1, 0 ) 2357203684 352338 0.00 0.00 0.00 0.00 0.00 0.00 1 drop table if exists shipamounts; drop table if exists ship1; drop table if exists ship2;