mysqlgroup-bycount

Query data and how many rows have that date in mysql


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);
}

Solution

  • Try the below -

    select reservation_date, count(*)
    from tablename
    group by reservation_date