Total sales for category custom column

gistfile1.php

<?php

public function subscribeEvents()
{
	Backend::$events->addEvent('shop:onExtendCategoryModel', $this, 'extend_category_model');
}


public function extend_category_model($category, $context)
{
	$total_sales = "SELECT IFNULL(count(shop_order_items.quantity)*shop_order_items.quantity, 0)
		FROM shop_products_categories
			LEFT JOIN shop_categories t1
				ON shop_products_categories.shop_category_id = t1.id
			LEFT JOIN shop_products
				ON shop_products_categories.shop_product_id = shop_products.id
			LEFT JOIN shop_order_items
				ON shop_products.id = shop_order_items.shop_product_id
			LEFT JOIN shop_orders
				ON shop_order_items.shop_order_id = shop_orders.id
		WHERE shop_products_categories.shop_category_id = shop_categories.id
		AND shop_orders.payment_processed IS NOT NULL
	";

	$category->define_column('total_sales', 'Total sales');
	$category->calculated_columns['total_sales'] = array(
		'sql' => $total_sales,
		'type' => db_number
	);
}