I ended up doing this in a custom module:
<?php
// Select nodes
$query = db_select('node', 'prod');
$query->condition('prod.type', 'product_display');
$query->fields('prod', array('nid', 'title'));
// Join product info link table
$query->join('field_data_field_product', 'fdcp', 'prod.nid = fdcp.entity_id' );
$query->fields('fdcp', array('field_product_product_id'));
// Join product info
$query->join('commerce_product', 'cp', 'fdcp.field_product_product_id = cp.product_id');
$query->fields('cp', array('sku'));
// Join category link table
$query->join('field_data_field_product_category', 'fdfpc', 'prod.nid = fdfpc.entity_id');
$query->fields('fdfpc', array('field_product_category_tid'));
// Join category data
$query->join('taxonomy_term_data', 'ttd', 'fdfpc.field_product_category_tid = ttd.tid');
$query->fields('ttd', array('name'));
// Join displaygroup data
$query->join('field_data_field_displaygroup', 'fdfd', 'prod.nid = fdfd.entity_id');
$query->fields('fdfd', array('field_displaygroup_value'));
// Grouping by the TERM id
$query->orderBy('ttd.tid');
$query->orderBy('fdfd.field_displaygroup_value');
$query->orderBy('prod.nid');
// build the query
$result = $query->execute();
// Initialize variables
?>
Then run trought my data with foreach.
Inside the loop:
<?php
$totalquery = db_select('commerce_line_item', 'cli');
$totalquery->condition('cli.line_item_label', $record->sku);
$totalquery->addExpression('SUM(quantity)', 'node_quantity');
$totalresult = $totalquery->execute();
?>
There will be a better solution with views I guess? But I could not figure it out!
