Magento: SQL query that lists all orders having more than one invoice

Tweet about this on TwitterShare on LinkedInShare on Google+Share on FacebookEmail this to someone

Recently, a friend developer who is new to Magento asked me: “How can I find all orders that have more than one invoice?”. His company’s accounting department have found that they have more than 1 invoice for some orders, but according to the company’s business rules, they must have exactly 1 invoice per order.

I offered my help and managed to write a very simple query that could help him.

Sql query that list all orders that have more than 1 invoice:

SELECT sales_flat_order.*, COUNT(sales_flat_invoice.order_id)
	FROM sales_flat_order, sales_flat_invoice
	WHERE sales_flat_order.entity_id = sales_flat_invoice.order_id
	GROUP BY sales_flat_invoice.order_id HAVING COUNT(*) > 1;

Hope that this helps somebody else!

Your thoughts / questions?

Tweet about this on TwitterShare on LinkedInShare on Google+Share on FacebookEmail this to someone

Tsvetan Stoychev

Tsvetan aka. Cecko is the founder of Cecko's Lab. He is Magento addicted since Magento CE 1.2.1.2 and has worked on over 30 Magento projects. At the moment he is in charge to take care about the money flow of the company, to keep constant communication with the clients and to keep the people in the office busy.

More Posts

Follow Me:
TwitterLinkedIn