with t1 as (
    select order_id,category_id,order_amount,order_date
    from order_details
    where order_date between '2024-01-01' and '2024-06-30'
)
select category_id,sum(order_amount) total_sales,
sum(if(customer_gender='男',1,0)) male_customers,
sum(if(customer_gender='女',1,0)) female_customers
from t1 join customer_info c on t1.order_id = c.order_id
group by category_id
order by category_id