How to Join two columns in same table

SELECT CAST(`last_charged_date`  AS DATE) AS Date_time, COUNT(*) AS 

Charged_Count, SUM(last_charge_amt) AS Revenue FROM subscriber GROUP BY CAST(last_charged_date AS DATE)

SELECT CAST(created_date AS DATE) AS Date_time, COUNT(*) AS Registered_Count FROM subscriber GROUP BY CAST(created_date AS DATE)

I want to join last_charged_date and created_date as one column and need show Charged_Count, Revenue, and Registered_Count as separate columns according to the date

Answer

You can use union all :

select date, 
       sum ( col = 'last_charged' ) as Charged_Count,
       sum (case when col = 'last_charged' then Revenue else 0 end ) as Revenue,
       sum ( col = 'created_date' ) as Registered_Count
from (select CAST(s.`last_charged_date`  AS DATE) as date, 
             s.`last_charge_amt` as Revenue, 'last_charged' as col 
      from `subscriber` s union all
      select CAST(s1.`created_date`  AS DATE) as created_date, 
             0, 'created_date' as col 
      from `subscriber` s1
    ) t
group by date;