LEFT JOIN with Date Format, SUM not working

• By -Hisated

LEFT JOIN with Date Format, SUM not working

• Posted on December 10, 2021
• Posted in Business

I have query like below is working fine

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 d.dt date, o.order_time AS date

from all_dates AS d
left join tbl_orders AS o on o.order_time = d.dt
order by d.dt

But I want extend it like below

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 d.dt date, o.DATE_FORMAT(order_time,'%d')AS date,o.SUM(CASE 
    WHEN seller_id = 1 
    THEN visitor_quantity 
    ELSE 0 
END) AS totalSold, o.SUM(CASE 
    WHEN buyer_id = 1 
    THEN visitor_quantity 
    ELSE 0 
END) AS totalBought

from all_dates d
left join tbl_orders o on o.DATE(order_time) = d.dt
order by d.dt

But its giving me error like below

so error is in o.DATE_FORMAT(order_time,'%d')AS date and if I remove that column, its giving me similar error on

o.SUM(CASE 
    WHEN seller_id = 1 
    THEN visitor_quantity 
    ELSE 0 
END) AS totalSold

I am new in mysql and i have checked various questions and answers but not able to solve the puzzle. Let me know if anyone here can help me for solve it. Thanks!