Muhammad Habib Muhammad Habib - 1 month ago 16
MySQL Question

MySQL Count row For distinct date distinct id

How can i make the first 4 rows become one row, and add one more column to state the value 4. The first four rows are same id and date but with different time. I don't know how to describe my question, but i think anyone could understand what i am trying to achieve. No JOINS needed because this operation is just on one table.

Figure

Including time column

Answer Source

You can combine the rows with an aggregate function, count in this case.

SELECT id, date, count(id)
FROM table_name
GROUP BY id, date

The GROUP BY id, date clause means that we will count rows where the id and the date is the same.