1

How would I apply this query to Magento to display results on page. Basically I want to show the top 10 users total sales purchase amount

SELECT customer_id,SUM(base_grand_total) AS total_sales 
FROM sales_flat_order
GROUP BY customer_id
ORDER BY total_sales DESC
LIMIT 10;
1

Try

$_collection = Mage::getModel('sales/order')->getCollection();
$_collection->getSelect()
            ->reset(Zend_Db_Select::COLUMNS)
            ->columns('customer_id')
            ->columns('SUM(base_grand_total) AS total_sales')
            ->order('total_sales')
            ->group('customer_id')
            ->limit(10);

or

/**
 * Get the resource model
 */
$resource = Mage::getSingleton('core/resource');

/**
 * Retrieve the read connection
 */
$readConnection = $resource->getConnection('core_read');

$query = 'SELECT customer_id,SUM(base_grand_total) AS total_sales 
          FROM sales_flat_order
          GROUP BY customer_id
          ORDER BY total_sales DESC
          LIMIT 10;';

/**
 * Execute the query and store the results in $results
 */
$results = $readConnection->fetchAll($query);

See https://stackoverflow.com/questions/5790811/aggregate-functions-in-magento-orm

1

Here is a quick little tutorial over a module called Clean SQL Reports. It lets you do arbitrary SQL reports in the magento admin section.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.