Sum a column in mysql

Question

I am trying to sum the column returned by the following query,

select
    max( CASE WHEN pm.meta_key = '_order_total' and p.ID = pm.post_id THEN pm.meta_value END ) as order_total
from
    wp_posts p 
    join wp_postmeta pm on p.ID = pm.post_id
    join wp_woocommerce_order_items oi on p.ID = oi.order_id
where
    post_type = 'shop_order' and
    post_date BETWEEN '2016-01-01' AND '2018-07-08' and
    post_status = 'wc-completed'
group by
    p.ID

I have tried the following,

select
    SUM(max( CASE WHEN pm.meta_key = '_order_total' and p.ID = pm.post_id THEN pm.meta_value END )) as order_total
from
    wp_posts p 
    join wp_postmeta pm on p.ID = pm.post_id
    join wp_woocommerce_order_items oi on p.ID = oi.order_id
where
    post_type = 'shop_order' and
    post_date BETWEEN '2016-01-01' AND '2018-07-08' and
    post_status = 'wc-completed'
group by
    p.ID

I am getting below error,

#1111 - Invalid use of group function

Answer 1

You can't use aggregate function over another aggregate function at same level, You could rewrite your query as

SELECT p.ID,
    SUM(pm.max_meta_value) AS order_total
FROM
    wp_posts p 
    JOIN (
        SELECT post_id, MAX(CASE WHEN meta_key = '_order_total' THEN meta_value END ) max_meta_value
        FROM wp_postmeta
        GROUP BY post_id
    ) pm ON p.ID = pm.post_id
    JOIN wp_woocommerce_order_items oi ON p.ID = oi.order_id
WHERE
    post_type = 'shop_order' AND
    post_date BETWEEN '2016-01-01' AND '2018-07-08' AND
    post_status = 'wc-completed'
GROUP BY
    p.ID