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 join customer_info using(order_id)
where order_date between '2024-01-01' and '2024-06-30'
group by category_id
order by category_id