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.
Including time column
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
GROUP BY id, date clause means that we will count rows where the id and the date is the same.