It's been ages since I've written complex SQL statements. I'm working in WordPress/WooCommerce and trying to sum orders against each customer.
When I started testing with this SQL statement, to just get a list of orders with customer info included, it works:
SELECT wp_users.ID as userID,
wp_posts.post_date,
wp_posts.post_type,
wp_users.user_email,
MAX(CASE WHEN wp_usermeta.meta_key = 'billing_first_name' THEN wp_usermeta.meta_value END) as firstName,
MAX(CASE WHEN wp_usermeta.meta_key = 'billing_last_name' THEN wp_usermeta.meta_value END) as lastName,
MAX(CASE WHEN wp_woocommerce_order_itemmeta.meta_key = '_line_total' THEN wp_woocommerce_order_itemmeta.meta_value END) as orderTotal
FROM wp_posts
INNER JOIN wp_postmeta
ON wp_postmeta.post_id = wp_posts.ID
INNER JOIN wp_users
ON wp_postmeta.meta_key = '_customer_user'
AND wp_users.ID = wp_postmeta.meta_value
INNER JOIN wp_usermeta
ON wp_usermeta.user_id = wp_users.ID
INNER JOIN wp_woocommerce_order_items
ON wp_woocommerce_order_items.order_id = wp_posts.ID
INNER JOIN wp_woocommerce_order_itemmeta
ON wp_woocommerce_order_itemmeta.order_item_id = wp_woocommerce_order_items.order_item_id
WHERE wp_posts.post_type IN ('shop_order','shop_subscription')
GROUP BY wp_posts.ID
Result is shown in the first image here: https://imgur.com/a/mZs9Iej
And if I sum those results in excel (there is only 1 test user so far), it shows $4281 as the total for that user.
If I then try and SUM in the SQL statement per user, like here (only two lines are changed, last MAX changed to SUM, and GROUP BY changed to wp_users.ID):
SELECT wp_users.ID as userID,
wp_posts.post_date,
wp_posts.post_type,
wp_users.user_email,
MAX(CASE WHEN wp_usermeta.meta_key = 'billing_first_name' THEN wp_usermeta.meta_value END) as firstName,
MAX(CASE WHEN wp_usermeta.meta_key = 'billing_last_name' THEN wp_usermeta.meta_value END) as lastName,
SUM(CASE WHEN wp_woocommerce_order_itemmeta.meta_key = '_line_total' THEN wp_woocommerce_order_itemmeta.meta_value END) as userTotal
FROM wp_posts
INNER JOIN wp_postmeta
ON wp_postmeta.post_id = wp_posts.ID
INNER JOIN wp_users
ON wp_postmeta.meta_key = '_customer_user'
AND wp_users.ID = wp_postmeta.meta_value
INNER JOIN wp_usermeta
ON wp_usermeta.user_id = wp_users.ID
INNER JOIN wp_woocommerce_order_items
ON wp_woocommerce_order_items.order_id = wp_posts.ID
INNER JOIN wp_woocommerce_order_itemmeta
ON wp_woocommerce_order_itemmeta.order_item_id = wp_woocommerce_order_items.order_item_id
WHERE wp_posts.post_type IN ('shop_order','shop_subscription')
GROUP BY wp_users.ID
It returns too large of a SUM of $340k instead of $4k, shown in second image here: https://imgur.com/a/mZs9Iej
Can anyone spot the error I made? Any help is appreciated!
try this one out
SELECT users.ID as userID,
posts.post_date,
posts.post_type,
users.user_email,
fname.meta_value as firstName,
lname.meta_value as lastName,
SUM(itemmeta.meta_value) as userTotal
FROM wp_posts posts
INNER JOIN wp_postmeta postmeta
ON postmeta.post_id = posts.ID
AND postmeta.meta_key = '_customer_user'
INNER JOIN wp_users users
ON users.ID = postmeta.meta_value
INNER JOIN wp_woocommerce_order_items items
ON items.order_id = posts.ID
INNER JOIN wp_woocommerce_order_itemmeta itemmeta
ON itemmeta.order_item_id = items.order_item_id
AND itemmeta.meta_key = '_line_total'
LEFT JOIN wp_usermeta fname
ON fname.user_id = users.ID
AND fname.meta_key = 'billing_first_name'
LEFT JOIN wp_usermeta lname
ON lname.user_id = users.ID
AND lname.meta_key = 'billing_last_name'
WHERE posts.post_type IN ('shop_order','shop_subscription')
GROUP BY users.ID
I introduced aliases for the tables and replaced your case constructs with left joins.
It seems that you need to specify the meta_keys as conditions because otherwise you will join rows that will not match the meta_key. This in turn will multiply the joined rows from wp_woocommerce_order_itemmeta and therefore the attributes you want to sum up.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments