LEFT JOIN with Date Format, SUM not working

  • Posted on
  • 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

enter image description here

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!

 powered by fun cram

BACK TO TOP