Count by column values (or) Cross-tab based query

select
            distinct lead_cand_party
            ,sum( case when status = 'Result Declared' then tot else 0 end ) as won
            ,sum( case when status = 'Pending' then tot else 0 end ) as lead
            ,sum( tot ) as total
from (
            select 
                             lead_cand_party,
                             status,
                             count(status) as tot
           from 
                             winning_leading_ae
           where 
                             st_name = 'TamilNadu'
           group by 
                             lead_cand_party,status
) l1
group by 
                 lead_cand_party
order by 
                 lead_cand_party




Alternatively you may use,


select
            t1.dist,
            t2.description,
            sum (case when t1.scheme = 'N' then tot else 0 end) as NMP,
            sum (case when t1.scheme = 'I' then tot else 0 end) as ICDS
from (
            select
                            substring(ddo_id,1,2) as dist,
                            scheme,
                            count(*) as tot
            from
                            m_nmc_icds
            group by
                            dist, scheme
            order by
                            dist
) t1

left join m_district t2 on t2.code = t1.dist
 
group by
                   t1.dist, t2.description
order by
                   t1.dist