I want the count of the orders which each customer has placed. Only those order which has non-zero subtotal should be counted. How can I get that data? Please let me know if we can use any SQL query

I have fetched data through query but it gives a count of all the orders, whereas I want only those orders which have non - zero value

$sql_results = "SELECT
    CONCAT(FORMAT(SUM(sales_flat_order.`grand_total`), 2)) AS 'Lifetime_Sales',
    COUNT(sales_flat_order.entity_id) AS 'Orders',
    customer_entity.email AS 'Email',
    customer_entity_varchar.value AS 'name',
    customer_address_entity_varchar.value AS 'Mobile'
FROM `customer_entity`
    LEFT JOIN sales_flat_order ON customer_entity.entity_id = sales_flat_order.customer_id
    LEFT JOIN customer_entity_varchar ON customer_entity.entity_id = customer_entity_varchar.entity_id
    LEFT JOIN customer_address_entity ON customer_entity.entity_id = customer_address_entity.parent_id
    LEFT JOIN customer_address_entity_varchar ON customer_address_entity.entity_id = customer_address_entity_varchar.entity_id
    WHERE customer_entity_varchar.attribute_id = 986 AND customer_address_entity_varchar.attribute_id = 17
GROUP BY customer_entity.entity_id
ORDER BY SUM(sales_flat_order.`grand_total`) DESC
LIMIT 10000 OFFSET 0";

1 Answer 1


I solved the problem by myself only. I had to put a condition of grand total greater than zero in where clause something like this

WHERE customer_entity_varchar.attribute_id = 986 AND customer_address_entity_varchar.attribute_id = 17 AND sales_flat_order.grand_total > 0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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