berkist34 berkist34 - 3 months ago 11
MySQL Question

Mysql count with condition

I have a table like this:

+---------------+--------+-----------------+
| ReservationID | UserID | ReservationDate |
+---------------+--------+-----------------+
| 1 | 10002 | 04_04_2016 |
| 2 | 10003 | 04_04_2016 |
| 3 | 10003 | 07_04_2016 |
| 4 | 10002 | 04_04_2016 |
| 5 | 10002 | 04_04_2016 |
| 6 | 10002 | 06_04_2016 |
+---------------+--------+-----------------+


I use this query to count how many reservation each of my user have:

SELECT UserID, COUNT(UserID) as Times FROM mytable GROUP BY UserID ORDER BY Times DESC


And It gives me this result:

+--------+-------+
| UserID | Times |
+--------+-------+
| 10002 | 4 |
| 10003 | 2 |
+--------+-------+


However I want to count one if my user has more than one reservation in specific date. For example User 10002 has 3 reservation at 04-04-2016, It should be count 1. And I want to get this result:

+--------+-------+
| UserID | Times |
+--------+-------+
| 10002 | 2 |
| 10003 | 2 |
+--------+-------+


How can I can get it?

Thanks

Answer

Use count with distinct instead:

SELECT UserID, COUNT(DISTINCT ReservationDate) as Times 
FROM mytable 
GROUP BY UserID 
ORDER BY Times DESC
Comments