图形显示的数据收集（SQL）

Question

What's the post efficient way to load data from database to generate report.

For example, if i want to have a graph of how many people sign up per day.

Information i would need is

Day 1 - > Amount users
Day 2 - > Amount users
Day 3 - > Amount users
Day 4 - > Amount users
Day 5 - > Amount users
Day 6 - > Amount users

But if its a 30 days, it would be 30 SQL query in my eyes, which i don't think its very efficient.

having MySQL query and then keep fetch out the result.

Is there a way you can doing this better and efficient to go about this? Thanks for any suggestion.

Table structure is simple:

id
username
password
salt
create_date
edit_date

Answer 1

This should work :

select count(distinct id) as amount
from users
group by year(create_date),month(create_date),day(create_date)