202b Jasper

SELECT implementing_partner, number_tested, When_tested_last_HIV, age_group, total, ROUND(((number_tested/total)*100), 0) as percentage
FROM

    ( SELECT
        (SELECT SUM(number_tested) number_tested FROM
            (SELECT count(id) AS number_tested,
            IFNULL(When_tested_last_HIV, 'Missing') AS When_tested_last_HIV, age_group, implementing_partner_id
            FROM stag_client_hiv_testing
            WHERE (
                (($P{IP} = NULL or $P{IP} IS NULL) and implementing_partner LIKE '%%')
                OR
                (implementing_partner =  $P{IP} )
                   )
            GROUP BY age_group,  CASE WHEN (($P{IP} = NULL or $P{IP} IS NULL) and implementing_partner LIKE '%%') THEN NULL ELSE implementing_partner END
            ORDER BY age_group)
        A WHERE A.age_group = B.age_group AND
        (
            (
                (($P{IP} = NULL or $P{IP} IS NULL) and implementing_partner LIKE '%%')
                OR
                (implementing_partner =  $P{IP} )
            )
        ))
    AS total,B.implementing_partner, B.number_tested, B.When_tested_last_HIV, B.age_group FROM
    (SELECT count(id) AS number_tested, IFNULL(When_tested_last_HIV, 'Missing') AS When_tested_last_HIV, age_group, implementing_partner
    FROM stag_client_hiv_testing
    WHERE
        (
            (($P{IP} = NULL or $P{IP} IS NULL) and implementing_partner LIKE '%%')
            OR
            (implementing_partner =  $P{IP} )
        )
    GROUP BY When_tested_last_HIV, age_group, CASE WHEN (($P{IP} = NULL or $P{IP} IS NULL) and implementing_partner LIKE '%%') THEN NULL ELSE implementing_partner END
    ORDER BY age_group) B)
C WHERE (
        (($P{IP} = NULL or $P{IP} IS NULL) and implementing_partner LIKE '%%')
        OR
        (implementing_partner =  $P{IP} )

    )