Unrecognized statement type. (near with)-CodePudding

I have query like below for get sum of purchase and sold traffic.

SELECT DATE_FORMAT(order_time,'%d')AS date,
    SUM(CASE WHEN seller_id = 1 THEN visitor_quantity ELSE 0 END) AS totalSold, 
    SUM(CASE WHEN buyer_id = 1 THEN visitor_quantity ELSE 0 END) AS totalBought 
FROM `tbl_orders` 
WHERE buyer_id = 1 OR seller_id = 1 
GROUP BY DATE(order_time)

Its working fine and giving me result like below

Since I am looking for get all dates between provided date range with 0 if there no any record for that date.

So I am trying following query

with recursive all_dates(dt) as (
    -- anchor
    select '2021-12-01' dt
        union all 
    -- recursion with stop condition
    select dt   interval 1 day from all_dates where dt   interval 1 day <= '2021-12-31'
)SELECT DATE_FORMAT(order_time,'%d')AS date,SUM(CASE 
    WHEN seller_id = 1 
    THEN visitor_quantity 
    ELSE 0 
END) AS totalSold, SUM(CASE 
    WHEN buyer_id = 1 
    THEN visitor_quantity 
    ELSE 0 
END) AS totalBought FROM `tbl_orders` WHERE buyer_id = 1 OR seller_id = 1 GROUP BY DATE(order_time)

I have found query tutorial

Let me know if anyone here can help me for same

Thanks!

CodePudding user response：

This is a PhpMyAdmin bug. You need to upgrade to 5.5.0 to get the fix.