CODEWITHSUNDEEP
mysqlsql
afriex
afriex

Reputation: 105

How to sum alias column table sql

I have query sql 

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

result:

enter image description here

how to sum colomn total??

Upvotes: 1

Views: 5017

Answers (5)

Zymon Castaneda
Zymon Castaneda

Reputation: 759

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

Upvotes: 0

El Gucs
El Gucs

Reputation: 967

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

Upvotes: 0

Zafar Malik
Zafar Malik

Reputation: 6844

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;

Upvotes: 1

Pholochtairze
Pholochtairze

Reputation: 1854

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;

Upvotes: 1

Jens
Jens

Reputation: 69440

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

Upvotes: 1

Related Questions