select
category_id 
,sum(order_amount) 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 
left join customer_info c on o.order_id = c.order_id
where order_date between '2024-01-01' and '2024-06-30'
group by 1
order by 1;