Magento Best Sellers - Configurable Products

configurable_best_sellers.sql

SELECT
	cpf.entity_id,
	cpf.name,
	cpf.type_id,
	(
		SELECT SUM(sbam.qty_ordered)
		FROM sales_bestsellers_aggregated_monthly sbam
		LEFT JOIN catalog_product_super_link cpsl
			ON cpsl.product_id = sbam.product_id
		WHERE sbam.product_id = cpf.entity_id
		OR cpsl.parent_id = cpf.entity_id
	) qty_ordered
FROM catalog_product_flat_1 cpf
INNER JOIN catalog_category_product_index ccpi
	ON ccpi.product_id = cpf.entity_id
	AND ccpi.store_id = 1
	AND ccpi.visibility IN (2, 4)
ORDER BY qty_ordered DESC;

SELECT 
    cpf.entity_id,
    cpf.name,
    cpf.type_id,
    cpsl.product_id,
    SUM(sbam.qty_ordered) qty_ordered
FROM catalog_product_flat_1 cpf
INNER JOIN catalog_category_product_index ccpi
    ON ccpi.product_id = cpf.entity_id
    AND ccpi.store_id = 1
    AND ccpi.visibility IN (2, 4)
LEFT JOIN catalog_product_super_link cpsl
    ON cpsl.parent_id = cpf.entity_id
LEFT JOIN sales_bestsellers_aggregated_monthly sbam
    ON cpsl.product_id = sbam.product_id
GROUP BY cpf.entity_id
ORDER BY qty_ordered DESC;

This is by far best of all above.

First iteration is REALLY slow. Second iteration ONLY works for simple products. This is quick and will account for simple, or configurable products

SELECT 
    cpf.entity_id,
    cpsl.product_id,
    cpf.name,
    cpf.type_id,
    SUM(sbam.qty_ordered) simple_qty_ordered,
    SUM(sbam2.qty_ordered) configurable_qty_ordered,
    IF(SUM(sbam.qty_ordered) IS NOT NULL, SUM(sbam.qty_ordered), SUM(sbam2.qty_ordered)) total_qty_ordered
FROM catalog_product_flat_1 cpf
LEFT JOIN sales_bestsellers_aggregated_monthly sbam
    ON cpf.entity_id = sbam.product_id
LEFT JOIN catalog_product_super_link cpsl
    ON cpsl.parent_id = cpf.entity_id
LEFT JOIN sales_bestsellers_aggregated_monthly sbam2
    ON cpsl.product_id = sbam2.product_id
WHERE sbam.store_id = 0 OR sbam.store_id IS NULL
GROUP BY cpf.entity_id
ORDER BY total_qty_ordered DESC;

Above does not account for grouped products, which are stored in catalog_product_link

catalog_product_relation might actually be the best bet

Here's the query we want using catalog_product_relation

SELECT 
    cpf.entity_id,
    cpr.child_id,
    cpf.name,
    cpf.type_id,
    SUM(sbam.qty_ordered) simple_qty_ordered,
    SUM(sbam2.qty_ordered) related_qty_ordered,
    IF(SUM(sbam.qty_ordered) IS NOT NULL, SUM(sbam.qty_ordered), SUM(sbam2.qty_ordered)) total_qty_ordered
FROM catalog_product_flat_1 cpf
LEFT JOIN sales_bestsellers_aggregated_monthly sbam
    ON cpf.entity_id = sbam.product_id
LEFT JOIN catalog_product_relation cpr
    ON cpr.parent_id = cpf.entity_id
LEFT JOIN sales_bestsellers_aggregated_monthly sbam2
    ON cpr.child_id = sbam2.product_id
WHERE sbam.store_id = 0 OR sbam.store_id IS NULL
GROUP BY cpf.entity_id
ORDER BY total_qty_ordered DESC;

If we don't need to add orders of child simple products we wind up with this...

SELECT 
    cpf.entity_id,
    cpf.name,
    cpf.type_id,
    SUM(sbam.qty_ordered) simple_qty_ordered
FROM catalog_product_flat_1 cpf
LEFT JOIN sales_bestsellers_aggregated_monthly sbam
    ON cpf.entity_id = sbam.product_id
WHERE sbam.store_id = 0 OR sbam.store_id IS NULL
GROUP BY cpf.entity_id
ORDER BY simple_qty_ordered DESC;