select category_id
,sum(coalesce(order_amount,0)) as total_sales
,sum(case when customer_gender = "男" then 1 else 0 end) as male_customers
,sum(case when customer_gender = "女" then 1 else 0 end) as female_customers
from order_details o
join customer_info c on o.order_id = c.order_id
where substring(order_date,1,7) between "2024-01" and "2024-06"
group by category_id