To Get the Multiple Values in a Diffent or Same Table

/******************************************************************************/
/* TITLE : Sub Query
/* DESCRIPTION     : To Get the Multiple Values in a Diffent or Same Table
/* CREATED BY   : Jemiris Selvi S
/* CREATED ON : 23-May-2017
/* MODIFIED BY : Jemiris Selvi S
/* MODIFIED ON : 23-May-2017
/******************************************************************************/

Example:

SELECT municipality,
       municipality_label                 AS COUNTY,
       Count(*)                           AS PURCHASED,
       ( Sum(CASE lrf_redeemed
               WHEN '1' THEN 1
               ELSE 0
             END) )                       AS REDEEMED,
       (SELECT Count(*)
        FROM   pmt_lien
        WHERE  judgement_date IS NOT NULL)AS JUDGEMENT_DATE,
       (SELECT Count(*)
        FROM   pmt_lien
        WHERE  write_off_date IS NOT NULL)AS WRITE_OFF_DATE
FROM   pmt_lien
WHERE  municipality IS NOT NULL
GROUP  BY municipality_label
ORDER  BY municipality_label

Reference : https://stackoverflow.com/questions/20410041/how-to-use-value-from-main-query-in-sub-query