mysql - Woocommerce : Get the total order value for each coupon use (SQL query)

Question

我正在使用以下问题的答案 https://stackoverflow.com/a/52852428/609630

SELECT pc.post_title AS coupon_name,
       pc.post_excerpt AS coupon_description,
       Max(CASE WHEN pmc.meta_key = 'discount_type'      AND  pc.`ID` = pmc.`post_id` THEN pmc.`meta_value` END) AS discount_type,
       Max(CASE WHEN pmc.meta_key = 'coupon_amount'      AND  pc.`ID` = pmc.`post_id` THEN pmc.`meta_value` END) AS coupon_amount,
       Max(CASE WHEN pmc.meta_key = 'product_ids'        AND  pc.`ID` = pmc.`post_id` THEN pmc.`meta_value` END) AS product_ids,
       Max(CASE WHEN pmc.meta_key = 'product_categories' AND  pc.`ID` = pmc.`post_id` THEN pmc.`meta_value` END) AS product_categories,
       Max(CASE WHEN pmc.meta_key = 'customer_email'     AND  pc.`ID` = pmc.`post_id` THEN pmc.`meta_value` END) AS customer_email,
       Max(CASE WHEN pmc.meta_key = 'usage_limit'        AND  pc.`ID` = pmc.`post_id` THEN pmc.`meta_value` END) AS usage_limit,
       Max(CASE WHEN pmc.meta_key = 'usage_count'        AND  pc.`ID` = pmc.`post_id` THEN pmc.`meta_value` END) AS total_usaged,
       po.ID AS order_id,
       MAX(CASE WHEN pmo.meta_key = '_billing_email'      AND po.ID = pmo.post_id THEN pmo.meta_value END) AS billing_email,
       MAX(CASE WHEN pmo.meta_key = '_billing_first_name' AND po.ID = pmo.post_id THEN pmo.meta_value END) AS billing_first_name,
       MAX(CASE WHEN pmo.meta_key = '_billing_last_name'  AND po.ID = pmo.post_id THEN pmo.meta_value END) AS billing_last_name,
       MAX(CASE WHEN pmo.meta_key = '_order_total'        AND po.ID = pmo.post_id THEN pmo.meta_value END) AS order_total
FROM `wp_posts` AS pc
INNER JOIN `wp_postmeta` AS pmc ON  pc.`ID` = pmc.`post_id`
INNER JOIN `wp_woocommerce_order_items` AS woi ON pc.post_title = woi.order_item_name
    AND woi.order_item_type = 'coupon'
INNER JOIN `wp_posts` AS po ON woi.order_id = po.ID
    AND po.post_type = 'shop_order'
    AND po.post_status IN ('wc-completed', 'wc-processing', 'wc-refunded') -- Added needed order status over here.
INNER JOIN `wp_postmeta` AS pmo ON po.ID = pmo.post_id
WHERE pc.post_type = 'shop_coupon'
GROUP BY po.ID
ORDER BY pc.ID DESC,
         po.ID DESC
LIMIT 0, 10 -- modify it accordingly.

但是当我尝试运行它时，出现以下错误:

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'totm.pc.post_title' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Error code 1055.

我该如何纠正它？答案被标记为正确，但我收到了上面的错误。

Answer 1

选择列表中存在的所有非聚合列必须出现在 GROUP BY 子句中。您收到此错误是因为您在选择列表中使用了一些非聚合列，但没有在 group by 子句中使用它们。尝试下面的查询 -

SELECT pc.post_title AS coupon_name,
       pc.post_excerpt AS coupon_description,
       Max(CASE WHEN pmc.meta_key = 'discount_type'      AND  pc.`ID` = pmc.`post_id` THEN pmc.`meta_value` END) AS discount_type,
       Max(CASE WHEN pmc.meta_key = 'coupon_amount'      AND  pc.`ID` = pmc.`post_id` THEN pmc.`meta_value` END) AS coupon_amount,
       Max(CASE WHEN pmc.meta_key = 'product_ids'        AND  pc.`ID` = pmc.`post_id` THEN pmc.`meta_value` END) AS product_ids,
       Max(CASE WHEN pmc.meta_key = 'product_categories' AND  pc.`ID` = pmc.`post_id` THEN pmc.`meta_value` END) AS product_categories,
       Max(CASE WHEN pmc.meta_key = 'customer_email'     AND  pc.`ID` = pmc.`post_id` THEN pmc.`meta_value` END) AS customer_email,
       Max(CASE WHEN pmc.meta_key = 'usage_limit'        AND  pc.`ID` = pmc.`post_id` THEN pmc.`meta_value` END) AS usage_limit,
       Max(CASE WHEN pmc.meta_key = 'usage_count'        AND  pc.`ID` = pmc.`post_id` THEN pmc.`meta_value` END) AS total_usaged,
       po.ID AS order_id,
       MAX(CASE WHEN pmo.meta_key = '_billing_email'      AND po.ID = pmo.post_id THEN pmo.meta_value END) AS billing_email,
       MAX(CASE WHEN pmo.meta_key = '_billing_first_name' AND po.ID = pmo.post_id THEN pmo.meta_value END) AS billing_first_name,
       MAX(CASE WHEN pmo.meta_key = '_billing_last_name'  AND po.ID = pmo.post_id THEN pmo.meta_value END) AS billing_last_name,
       MAX(CASE WHEN pmo.meta_key = '_order_total'        AND po.ID = pmo.post_id THEN pmo.meta_value END) AS order_total
FROM `wp_posts` AS pc
INNER JOIN `wp_postmeta` AS pmc ON  pc.`ID` = pmc.`post_id`
INNER JOIN `wp_woocommerce_order_items` AS woi ON pc.post_title = woi.order_item_name
    AND woi.order_item_type = 'coupon'
INNER JOIN `wp_posts` AS po ON woi.order_id = po.ID
    AND po.post_type = 'shop_order'
    AND po.post_status IN ('wc-completed', 'wc-processing', 'wc-refunded') -- Added needed order status over here.
INNER JOIN `wp_postmeta` AS pmo ON po.ID = pmo.post_id
WHERE pc.post_type = 'shop_coupon'
GROUP BY pc.post_title,
         pc.post_excerpt,
         po.ID
ORDER BY po.ID DESC
LIMIT 0, 10