Charlie Harvey

Tip: find which payment method was used for Magento orders with SQL

Today we wanted to work out which payment type had been used for a range of orders on the Ethical Shop. The quickest way I could think of was to dive into MariaDB/MySQL and issue a quesry on the sales_flat_order and sales_flat_order_payments tables for a range of orders.

You can use the increment_id field to specify the first and last orders that you are interested in, the method field tells you which payment type was used.

Here is the SQL you need to create the report with the most popular payment method first.

SELECT p.method, count(o.increment_id) AS num FROM sales_flat_order AS o, sales_flat_order_payment AS p WHERE o.entity_id = p.parent_id AND o.increment_id >= first_order_increment AND o.increment_id <= last_order_increment GROUP BY p.method ORDER BY num desc;

Note that you will need to replace first_order_increment and last_order_increment with the first and last order increments. You can find these by looking at the order numbers that you are interested in the Magento backend.


  • Be respectful. You may want to read the comment guidelines before posting.
  • You can use Markdown syntax to format your comments. You can only use level 5 and 6 headings.
  • You can add class="your language" to code blocks to help highlight.js highlight them correctly.

Privacy note: This form will forward your IP address, user agent and referrer to the Akismet, StopForumSpam and Botscout spam filtering services. I don’t log these details. Those services will. I do log everything you type into the form. Full privacy statement.