CODEWITHSUNDEEP
sql
Martijn Hendrikx
Martijn Hendrikx

Reputation: 59

How can I merge two rows with similar values?

this is probably a really basic question, but I can't seem to figure it out. I have the following query:

SELECT COUNT(wp_woocommerce_order_itemmeta.meta_key) as Aantal, wp_woocommerce_order_itemmeta.meta_value as Formule FROM wp_woocommerce_order_itemmeta 
LEFT JOIN wp_woocommerce_order_items 
ON wp_woocommerce_order_itemmeta.order_item_id = wp_woocommerce_order_items.order_item_id
LEFT JOIN wp_posts
ON wp_woocommerce_order_items.order_id = wp_posts.ID
WHERE wp_woocommerce_order_itemmeta.meta_key = 'pa_formule'
GROUP BY wp_woocommerce_order_itemmeta.meta_value
HAVING COUNT(wp_woocommerce_order_itemmeta.meta_key) > 10

Which returns a table similar to this:

3305   all-in-one
5454   curling
1200   length-define
800    length-define-2
920    define-length

I want to combine the values of 'Formule' that are similar (the bottom 3), to create the following table:

3305   all-in-one
5454   curling
2920   length-define

Most answers I found only worked using group by, but that required the values of 'Formule' to be the same, but in this case they aren't. Thanks!

Upvotes: 1

Views: 38

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Use a case expression to define the column you want to aggregate by:

SELECT COUNT(woim.meta_key) as Aantal,
       (CASE WHEN woim.meta_value IN ('length-define', 'length-define-2', 'define-length') THEN 'length-define'
             ELSE woim.meta_value
        END) as Formule
FROM wp_woocommerce_order_itemmeta  woi mLEFT JOIN
     wp_woocommerce_order_items woi
     ON woim.order_item_id = woi.order_item_id LEFT JOIN
     wp_posts p
     ON woi.order_id = p.ID
WHERE woim.meta_key = 'pa_formule'
GROUP BY Formule
HAVING COUNT(woim.meta_key) > 10;

Not all databases support column aliases in the GROUP BY. If yours does not, you need to repeat the CASE expression.

Upvotes: 1

Related Questions