BiTel

SELECT
    tp.id,
    tp.title AS plan_title,
    COUNT(DISTINCT ct.cid) AS number_of_users
FROM
    contract_tariff AS ct
    LEFT JOIN
    tariff_plan AS tp
    ON
    tp.id = ct.tpid
    LEFT JOIN
    contract AS c
    ON
    c.id = ct.cid
WHERE
    (
        (ct.date1 IS NULL OR ct.date1 <= DATE(NOW()))
        AND
        (ct.date2 IS NULL OR ct.date2 >= DATE(NOW()))
    )
    AND
    !(tp.id IS NULL)
    AND
    !(c.id IS NULL)
GROUP BY ct.tpid
ORDER BY plan_title