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

Adding a WHERE clause on a GROUP BY operation – Woocommerce

This query lists out the distinct order numbers in Woocommerce that have order either of two products, where the orders are in a status called ‘wc-on-hold’. The query does work, but if I comment back in the AND COUNT(t1.ID)=2 it fails with an invalid use of group function. If I change the WHERE statement to HAVING, it still fails with a syntax error.

select distinct t1.id, count(t1.ID) as Products from wp_posts t1
inner join wp_woocommerce_order_items t2 on t1.ID = t2.order_id
inner join wp_woocommerce_order_itemmeta t3 on t2.order_item_id = t3.order_item_id
where t3.meta_key = '_product_id' and (t3.meta_value=19549 or t3.meta_value=19547)
and t1.post_status='wc-on-hold'
--and count(t1.ID)=2
group by t1.id
order by count(t1.ID) asc
Tags:

2

Answers


  1. 0

    It sounds like a having clause is what you want. I would phrase your query as:

    select p.id, count(*) as no_products 
from wp_posts p
inner join wp_woocommerce_order_items oi 
    on p.ID = oi.order_id
inner join wp_woocommerce_order_itemmeta oim 
    on oi.order_item_id = oim.order_item_id
where 
    oim.meta_key = '_product_id' 
    and oim.meta_value in (19549, 19547)
    and p.post_status = 'wc-on-hold'
group by p.id
having count(*) = 2
order by p.id

    Notes:

    • the having clause goes after the group by clause

    • meaningful table aliases make the query easier to follow

    • in comes handy to check the same column against a list of values

    • count(*) is more efficient than count(<column>) (and, here, it is functionaly equivalent)

    • ordering by the count is useless, since you are filtering on it already (all rows have the same count)

    Login or Signup to reply.
  2. 0

    Where did you put the having clause when you tried it?
    A Having clause needs to be after the group by and before the Order By.
    So it would be like this:

    select distinct t1.id, count(t1.ID) as Products from wp_posts t1
inner join wp_woocommerce_order_items t2 on t1.ID = t2.order_id
inner join wp_woocommerce_order_itemmeta t3 on t2.order_item_id = t3.order_item_id
where t3.meta_key = '_product_id' and (t3.meta_value=19549 or t3.meta_value=19547)
and t1.post_status='wc-on-hold'
group by t1.id
having count(t1.id) = 2
order by count(t1.ID) asc
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search