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?