Count and Display Records from different PHP/MYSQL tables

Question

GOOD AFTERNOON. I am creating a report to tell and display: - the number of men and women (table: Customer) who made booking (table booking) at a hotel in a given period of time (passed via form). My question is:

How to count and display using Count or even, it is possible to count and automatically divide by sex the survey?

EXAMPLE OF OUTPUT

MEN: x
WOMEN: y
TOTAL: x + y

SQL to count women

SELECT COUNT(*) FROM booking 
INNER JOIN customer ON booking.customer_id = customer.id 
WHERE customer.gender='Female'

SQL to count the men

SELECT COUNT(*) FROM booking 
INNER JOIN customer ON booking.customer_id = customer.id 
WHERE customer.gender='Male'

SQL to set the date range

SELECT * FROM booking 
INNER JOIN room ON booking.room_id = room.id 
WHERE (checkin < '$datainicialsql' AND checkout >= '$datainicialsql') OR 
((checkin >= '$datainicialsql' AND checkin <= '$datafinalsql') 
AND checkout <= '$datafinalsql') 
OR ((checkin >= '$datainicialsql' AND checkin <= '$datafinalsql') AND checkout > '$datafinalsql') 
OR (checkin < '$datainicialsql' AND checkout > '$datafinalsql')"

General.php

    // PEGA A DATA DIGITADA VIA FORM
$dateStart = $_POST['datainicial'];
$dateEnd = $_POST['datafinal'];

// VARIÁVEL AUXILIAR PARA DATA
$datainicial = $dateStart;
$datafinal = $dateEnd;

// CRIA E EXIBE A ARRAY COM A RANGE DE DATA ESCOLHIDA VIA FORM
$dateStart = implode('-', array_reverse(explode('/', substr($dateStart, 0, 10)))) . substr($dateStart, 10);
$dateStart = new DateTime($dateStart);

$dateEnd = implode('-', array_reverse(explode('/', substr($dateEnd, 0, 10)))) . substr($dateEnd, 10);
$dateEnd = new DateTime($dateEnd);

//IMPRIME INTERVALO
$dateRange = array();
while ($dateStart <= $dateEnd) {
    $dateRange[] = $dateStart->format('d-m-Y');
    $dateStart = $dateStart->modify('+1day');
}

$dias = count($dateRange);

//CONVERTE DATA FORMATO SQL
$datainicialsql = date("Y-m-d", strtotime($datainicial));
$datafinalsql = date("Y-m-d", strtotime($datafinal));

// SELECIONA OS BOOKINGS QUE TEM A DATA RELACIONADA AO INTERVALO
$result = mysql_query("SELECT COUNT(*) FROM booking LEFT JOIN customer ON booking.customer_id = customer.id WHERE (checkin < '$datainicialsql' AND checkout >= '$datainicialsql') OR 
((checkin >= '$datainicialsql' AND checkin <= '$datafinalsql') AND checkout <= '$datafinalsql') OR
((checkin >= '$datainicialsql' AND checkin <= '$datafinalsql') AND checkout > '$datafinalsql') OR
(checkin < '$datainicialsql' AND checkout > '$datafinalsql') AND (customer.gender='Male') ") or die(mysql_error());

Answer 1

You can try it like this:

SELECT Gender, 
       COUNT(DISTINCT CASE WHEN GENDER = 'Male' THEN booking.customer_id END) [#Male],
       COUNT(DISTINCT CASE WHEN GENDER = 'Female' THEN booking.customer_id END) [#Female],
       COUNT(DISTINCT booking_customer_id) [#Total]
FROM booking 
INNER JOIN room  
   ON booking.room_id = room.id 
INNER JOIN customer 
   ON booking.customer_id = customer.id 
WHERE (checkin < '$datainicialsql' AND checkout >= '$datainicialsql') OR 
((checkin >= '$datainicialsql' AND checkin <= '$datafinalsql') 
AND checkout <= '$datafinalsql') 
OR ((checkin >= '$datainicialsql' AND checkin <= '$datafinalsql') AND checkout > '$datafinalsql') 
OR (checkin < '$datainicialsql' AND checkout > '$datafinalsql')"
GROUP BY Gender

You may have to adjust the junctions because I don’t know the structure of the tables but it should work.