Need help with sql query, grouping and union/join?

I have a mysql table with 3 columns:

• id [int]
• name [varchar]
• time [timestamp CURRENT_TIMESTAMP]
• flag [boolean]

I am using a query like this to group the results by day:

SELECT COUNT(DISTINCT name), DATE(time) as day FROM `table` GROUP BY day

That works fine, but what I really want to get COUNT(DISTINCT name) WHERE FLAG = 0 and COUNT(DISTINCT na开发者_如何学JAVAme) WHERE FLAG = 1 in the same query and group them by day.

How can I do that?

Then you have to apply grouping first on flag then on day

SELECT COUNT(DISTINCT name), DATE(time) as day FROM `table` GROUP BY flag,day

Your question is not very clear but I think this might help.. SELECT COUNT(DISTINCT name), DATE(time) as day FROM table GROUP BY day HAVING FLAG=0 and FLAG=1

Or you can manipulate this query as you want to group..

I am wondering that you want to create the result with a stored procedure or only a select query?

"date" 
"count number of flag = 1" 
"count number of flag =0"

You can

1. create 2 template tables and insert results from query "flag =1" and query of "flag = 0" into them
2. join 2 template tables on "date".