For instance, if a table has the following rows:
id, reservation date:
1 1/1/2020
2 1/1/2020
3 2/1/2020
Id like to be able to create a query that returns each unique date in the table and how many times it occurs. So the result from the following table would be
1/1/2020 , 2
2/1/2020 , 1
The only way I can think of is using multiple queries but there could be an easier way that I'm unaware of
WHAT I HAVE SO FAR
function getBookingEventInfo($connection) {
$dates = array();
$query = "SELECT DISTINCT reservation_date FROM bookings";
if($stmt = $connection->prepare($query)){
$stmt->execute();
$stmt->bind_result($date);
while($stmt->fetch()){
array_push($dates, $date);
}
$stmt->close();
}
print_r($dates);
}
Try the below -
select reservation_date, count(*)
from tablename
group by reservation_date