`order_id` int(11) NOT NULL AUTO_INCREMENT,

`order_status_id` int(11),

PRIMARY KEY (`order_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `oc_order_total` (

`order_total_id` int(10) NOT NULL AUTO_INCREMENT,

`order_id` int(11) NOT NULL,

`code` varchar(32) NOT NULL,

`title` varchar(255) NOT NULL,

`text` varchar(255) NOT NULL,

`value` decimal(15,4) NOT NULL DEFAULT '0.0000',

`sort_order` int(3) NOT NULL,

PRIMARY KEY (`order_total_id`),

KEY `order_id` (`order_id`),

KEY `code` (`code`),

KEY `value` (`value`),

KEY `order_id_code` (`order_id`,`code`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `oc_order` (`order_id`, `order_status_id`)

(100053000, 1),

(100053001, 1),

(100054000, 1);

INSERT INTO `oc_order_total` (`order_total_id`, `order_id`, `code`, `title`, `text`, `value`, `sort_order`)

(1, 100053000, 'misc', 'Shipping Insurance', '$9.54', 9.5398, 4),

(2, 100054000, 'misc', 'Shipping Insurance', '$2.91', 2.9051, 4);

o.order_id,

insurance.value AS insurance,

insurance.order_id

,max(insurance.value),

group_concat(insurance.order_id)

FROM `oc_order` o

LEFT JOIN oc_order_total insurance ON insurance.order_id = o.order_id AND insurance.code = 'misc'

WHERE order_status_id > 0

GROUP BY o.order_id;