Woocommerce Question posted in
The official Woocommerce documentation can be found here.

SQL query to count number of Woocommerce orders by a list of users

Using a SQL query, is it possible to count the number of Woocommerce orders for each user id?

SELECT customer_id FROM subscriptions
WHERE status = 'cancelled' GROUP BY customer_id

I’ve generated the list above of user id’s, now I need to find out how many orders each one has. How can I do that using an SQL query?

Tags:

2

Answers


  1. 0

    Have you tried with count:

    column_to_count is the column you want to count(for example orders maybe…)

    SELECT customer_id, count(column_to_count)
FROM subscriptions
WHERE status = 'cancelled' GROUP BY customer_id

    Then you can use a left join

    select sub.customer_id, count(1)
from subscriptions sub
left join wp_posts wp
on wp.id = sub.customer_id
where sub.status = 'cancelled';

    Here is a small demo so you can show us if somethin is different.

    Login or Signup to reply.
  2. 0

    You can use correlated sub-query : 

    SELECT s.customer_id, count(*) AS Total,
       (SELECT COUNT(*)
        FROM subscriptions s
        WHERE wp.id = s.customer_id AND s.status = 'cancelled' 
       ) AS cancelledorders
FROM wp_posts p
GROUP BY s.customer_id;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search