-- ====================================================================
-- OPTIMIZED VERSION: AWC Creches Monthly Data Export Query
-- Using LEFT JOINs for better performance
-- ====================================================================

-- Set the month number here (1-12)
SET @month_number = 9;

SELECT 
    an.awc_center_code AS 'AWC Center Code',
    an.name AS 'Anganwadi Name',
    @month_number AS 'Month',
    COALESCE(c1.count, 0) AS 'Children 0-6 Months Count',
    COALESCE(c2.count, 0) AS 'Children 6 Months-3 Years Count',
    COALESCE(c3.count, 0) AS 'Children 3-6 Years Count',
    (COALESCE(c1.count, 0) + COALESCE(c2.count, 0) + COALESCE(c3.count, 0)) AS 'Total Children Count'
FROM anganwadi_names an
LEFT JOIN (
    SELECT awc_code, COUNT(*) as count 
    FROM children_0m_6m 
    WHERE monthupdated = @month_number
    GROUP BY awc_code
) c1 ON an.awc_center_code = c1.awc_code
LEFT JOIN (
    SELECT awc_code, COUNT(*) as count 
    FROM children_6m_3y 
    WHERE monthupdated = @month_number
    GROUP BY awc_code
) c2 ON an.awc_center_code = c2.awc_code
LEFT JOIN (
    SELECT awc_code, COUNT(*) as count 
    FROM children_3y_6y 
    WHERE monthupdated = @month_number
    GROUP BY awc_code
) c3 ON an.awc_center_code = c3.awc_code
ORDER BY an.awc_center_code;
