How to sum alias column table sql

This query should work:

select sum(a.total) from (
select date_format(created_at,'%Y-%m-%d') as date,
(select count(*)  from tb_media where date_format(created_at,'%Y-%m-%d') = date ) as total
from tb_media group by date) as a

Well, you have 2 possible queries. The simple one (if you want the total you don't need the rest of the query) :

SELECT COUNT(*) FROM tb_media

The less simple one :

SELECT SUM(t.total)
FROM (select date_format(created_at,'%Y-%m-%d') as date,
(select count(*)  from tb_media where date_format(created_at,'%Y-%m-%d') = date ) as total
from tb_media group by date) AS t;

If I am understanding your requirement then you need to count records based on date wise and also want their sum at the end and your created_on column is date type. If it is correct then you can use below query-

SELECT IFNULL(DATE_FORMAT(created_at,'%Y-%m-%d'),'Total') AS rep_date, 
COUNT(*) total  FROM tb_media 
GROUP BY created_at WITH ROLLUP;

Not sure what or how you want your output data, but my best guess is you want a tabulated list with the date and the counts of each day AND a sum at the end of the table? If it is so, this will work:

(select date_format(created_at,'%Y-%m-%d') as date,count(*) 
from tb_media group by date) 
union
(select 'Total',count(*) from tb_media)

or if you want only the sum of all the records, the query would be

SELECT COUNT(*) FROM tb_media

You can try this mate:

SELECT
    DATE_FORMAT(created_at, '%Y-%m-%d') AS `date`,
    COUNT(<put_table_key_here>) AS `total`
FROM
    tb_media
GROUP BY
    `date`;  

Note

• Use the primary key or any key in the table to count the said records
• Atleast, avoid using subquery when you expect performance in your script