CODEWITHSUNDEEP
mysqldatetime
Anil Singh
Anil Singh

Reputation: 100

Mysql query to count result by removing time from datetime

I am trying to write one query in which i need to fetch/count records which are registered on same date. But the issue is that in mysql structure created_date field have "datetime" structure.

Let me give you example

If 5 people are registered on 2015-02-25 and 6 people registered on 2015-02-11. It will output as

Sno. Date.           count
1)   2015-02-25        5  
2)   2015-02-11        6

Here is sample of attached database rows for better understanding https://i.sstatic.net/iPeLl.png

Upvotes: 0

Views: 541

Answers (3)

jogesh_pi
jogesh_pi

Reputation: 9782

your query should be like this: 

select date(created_at) created_at, count(*) from TABLE 
    group by date(created_at)

Select between dates 

select date(created_at) created_at, count(*) from TABLE 
    where date(created_at) >= '2015-02-11' and date(created_at) <= '2015-02-25' 
    group by date(created_at)

With between: 

select date(created_at) created_at, count(*) from Mytable  
   where date(created_at) BETWEEN '2015-01-05' AND '2015-02-25' 
   group by date(created_at)

Referrence: count()

Upvotes: 0

1000111
1000111

Reputation: 13519

It might be the one that you expected.

SELECT
    DATE_FORMAT(created_at,"%Y-%m-%d") AS Date,
COUNT(*) AS count
FROM table_name
GROUP BY DATE_FORMAT(created_at,"%Y-%m-%d")
ORDER BY created_at DESC;

This query will count the registered people in each day. Of course the latest registration will come first.

Upvotes: 0

ReallyMadeMeThink
ReallyMadeMeThink

Reputation: 1071

SELECT date(created_at),count(*) FROM myTable GROUP BY date(created_at)

Upvotes: 1

Related Questions