custom-reports.sql

/* Here are some example custom report queries for use with the custom 
 * reports module 
 * 
 * http://magemail.co/free-custom-reports-module-for-magento/ 
 */

/*
 * Number of items in order
 * 
 * Useful to find out whether customers are buying a given product
 * standalone or with other products.
 */
SELECT i.qty_ordered AS `Items in Order`, 
    COUNT(*) AS `Number of Orders`, 
    SUM(base_grand_total) AS `Total Revenue`
FROM sales_flat_order AS o
LEFT JOIN sales_flat_order_item AS i ON i.order_id = o.entity_id
WHERE i.`sku` IN ('SKU')
GROUP BY i.qty_ordered
LIMIT 500