Desper
January 15, 2021
103 views
1 vote
2 Answers

I’m stuck in this query for while and any type of help would be appreciated. This is for a wordpress website which uses woocommerce.

Trying to get purchase details of customers and I need to get a result set which should contain following fields.

Email | First Name | Orders | Items Purchased | Order Total

I’ve written the following SQL for this purpose. achg8 is the table prefix. An order may have one or more items.

SELECT  
 pm1.meta_value as email , 
 pm2.meta_value as first_name, 
 sum(pm3.meta_value) as total,  
 count(posts.ID) as orders , 
 count(items.order_item_id) as items

from achg8_posts as posts 

left join achg8_postmeta as pm1 on posts.ID = pm1.post_id 
left join achg8_postmeta as pm2 on posts.ID = pm2.post_id
left join achg8_postmeta as pm3 on posts.ID = pm3.post_id
left join achg8_woocommerce_order_items as items on items.order_id = posts.ID

WHERE

    posts.post_type = 'shop_order' and 
    posts.post_status = 'wc-processing' and 
    pm1.meta_key='_billing_email' and 
    pm2.meta_key='_billing_first_name' and 
    pm3.meta_key='_order_total'
    
GROUP by email

The problem is order number and item numbers are always equal. but that’s not the case actually.

Need and insight on what i’am doing wrong.

UPDATE

edited the sql to the following as per @matigo’s comment. The problem now is the order total gets added up multiple times. For an example if an order has two items (i.e two rows in the joined resultset) order-total gets added up twice.

SELECT
    pm1.meta_value as email , 
    pm2.meta_value as first_name, 
    sum(pm3.meta_value) as total,  
    count(DISTINCT posts.ID) as orders , 
    count(items.order_item_id) as items

from achg8_posts as posts 

left join achg8_postmeta as pm1 on posts.ID = pm1.post_id 
left join achg8_postmeta as pm2 on posts.ID = pm2.post_id
left join achg8_postmeta as pm3 on posts.ID = pm3.post_id
left join achg8_woocommerce_order_items as items on items.order_id = posts.ID

WHERE

    posts.post_type = 'shop_order' and 
    posts.post_status = 'wc-processing' and 
    pm1.meta_key='_billing_email' and 
    pm2.meta_key='_billing_first_name' and 
    pm3.meta_key='_order_total'
    
GROUP by email

Answers

Assuming that the same order number can appear in items multiple times, it looks like you need a DISTINCT in your COUNT.

Try this:

SELECT pm1.`meta_value` as `email`, 
       pm2.`meta_value` as `first_name`, 
       SUM(pm3.`meta_value`) as `total`,  
       COUNT(posts.`ID`) as `orders`,
       (SELECT COUNT(z.`order_item_id`)
          FROM `achg8_woocommerce_order_items` z
         WHERE posts.`ID` = z.`order_id`) as `items`
  FROM `achg8_posts` posts INNER JOIN `achg8_postmeta` pm1 ON posts.`ID` = pm1.`post_id`
                           INNER JOIN `achg8_postmeta` pm2 ON posts.`ID` = pm2.`post_id`
                           INNER JOIN `achg8_postmeta` pm3 ON posts.`ID` = pm3.`post_id`
 WHERE posts.`post_type` = 'shop_order'
   and posts.`post_status` = 'wc-processing'
   and pm1.`meta_key` = '_billing_email'
   and pm2.`meta_key` = '_billing_first_name'
   and pm3.`meta_key` = '_order_total'
 GROUP by `email`, `first_name`

This should give you exactly what you’re looking for 👍🏻

Can you try this and see if it works.

Sorry, I removed that joins without considering the relation with meta_key, can you try this.

SELECT 
 pm1.`meta_value` as `email`, 
 pm2.`meta_value` as `first_name`, 
 SUM(pm3.`meta_value`) as `total`,  
 COUNT(distinct posts.`ID`) as `orders`,
 COUNT(distinct items.order_item_id) as `items`
FROM `achg8_posts` posts 
 INNER JOIN `achg8_postmeta` pm1 ON posts.`ID` = pm1.`post_id`
 INNER JOIN `achg8_postmeta` pm2 ON posts.`ID` = pm2.`post_id`
 INNER JOIN `achg8_postmeta` pm3 ON posts.`ID` = pm3.`post_id`
 INNER JOIN achg8_woocommerce_order_items as items on items.order_id = posts.ID
WHERE 
 posts.`post_type` = 'shop_order' and
 posts.`post_status` = 'wc-processing' and
 pm1.`meta_key` = '_billing_email' and
 pm2.`meta_key` = '_billing_first_name' and
 pm3.`meta_key` = '_order_total'
GROUP by `email`

Please signup or login to give your own answer.

Click here to cancel reply.

Getting number of purchased items using SQL – Woocommerce

UPDATE

Answers