with od as(
select o.order_id,category_id ,order_amount,order_date,customer_gender
from order_details o
left join customer_info c
on o.order_id = c.order_id
where order_date between '2024-01-01' and  '2024-06-30'
)

select od.category_id
, sum(order_amount) total_sales
, sum(if(customer_gender='男',1,0)) male_customers
, sum(if(customer_gender='女',1,0)) female_customers
from od
group by 1