MySQL Query to Get All Coupon

MySQL query to list all the published WooCommerce Coupon.

One of my answer in StackOverflow on Question How WooCommerce Coupons are stored in Database? has got many hits, So I thought to put a whole query in this tutorial which might help other developers.

SELECT p.`ID`, 
       p.`post_title`   AS coupon_code, 
       p.`post_excerpt` AS coupon_description, 
       Max(CASE WHEN pm.meta_key = 'discount_type'      AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS discount_type,			-- Discount type 
       Max(CASE WHEN pm.meta_key = 'coupon_amount'      AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS coupon_amount,			-- Coupon amount 
       Max(CASE WHEN pm.meta_key = 'free_shipping'      AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS free_shipping,			-- Allow free shipping 
       Max(CASE WHEN pm.meta_key = 'expiry_date'        AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS expiry_date,				-- Coupon expiry date 
       Max(CASE WHEN pm.meta_key = 'minimum_amount'     AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS minimum_amount,			-- Minimum spend 
       Max(CASE WHEN pm.meta_key = 'maximum_amount'     AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS maximum_amount,			-- Maximum spend 
       Max(CASE WHEN pm.meta_key = 'individual_use'     AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS individual_use,			-- Individual use only 
       Max(CASE WHEN pm.meta_key = 'exclude_sale_items' AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS exclude_sale_items,			-- Exclude sale items 
       Max(CASE WHEN pm.meta_key = 'product_ids' 	AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS product_ids,				-- Products 
       Max(CASE WHEN pm.meta_key = 'exclude_product_ids'AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS exclude_product_ids, 		-- Exclude products 
       Max(CASE WHEN pm.meta_key = 'product_categories' AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS product_categories, 			-- Product categories 
       Max(CASE WHEN pm.meta_key = 'exclude_product_categories' AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS exclude_product_categories,- Exclude Product categories 
       Max(CASE WHEN pm.meta_key = 'customer_email'     AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS customer_email,			-- Email restrictions 
       Max(CASE WHEN pm.meta_key = 'usage_limit' 	AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS usage_limit,				-- Usage limit per coupon 
       Max(CASE WHEN pm.meta_key = 'usage_limit_per_user' 	AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS usage_limit_per_user,	-- Usage limit per user 
       Max(CASE WHEN pm.meta_key = 'usage_count' 	AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS total_usaged 			       -- Usage count 
FROM   `wp_posts` AS p 
       INNER JOIN `wp_postmeta` AS pm ON  p.`ID` = pm.`post_id` 
WHERE  p.`post_type` = 'shop_coupon' 
       AND p.`post_status` = 'publish' 
GROUP  BY p.`ID` 
ORDER  BY p.`ID` ASC;

Let me know in the comment if this helps you or not if it helps then do hit the Facebook like button and share with other WooCommerce developers.

Raunak Gupta

Raunak Gupta

I'm Raunak Gupta, a seasoned software developer with over 9 years of experience in a wide range of programming languages, frameworks, and tools. I started my journey as a WordPress & CakePHP developer in 2014, diving deep into the world of OOPs, Request handling, and SEO. Along the way, I crafted numerous dazzling WooCommerce stores, tamed payment gateways, optimized for full filament functionality, and achieved ultra-low latency for lightning-fast load times. My expertise extends to BI tools, website builders, DevOps, and team leadership. I like to help upcoming developers, so I share my experience through this blog and by assisting fellow developers on Stack Overflow, where I've earned a stellar reputation with over 10k+ points of recognition.

Articles: 29

8 Comments

  1. Hi, this is great, thank you!
    Is it possible to somehow also add detail of the customer that used the coupon? Like their name or email address?
    all my coupons has a usage limit of 1.

    Thank you
    Schalk

  2. Thank you for this, how do I update the minimum amount field?

    I got ‘Unknown column ‘minimum_amount’ in ‘field list’ All changes were reverted (DDL statements can’t be reverted)’ when I edited using TablePlus directly from the output record.

    Thank you !

Leave a Reply

Your email address will not be published. Required fields are marked *