kanniyappan r kanniyappan r - 1 month ago 8
JSON Question

How to take two counts result from second table using MySQL JOIN

Here I have two tables, (

trip_details
&
trip_member
), my requirement is based on
tripId
. I want to get the employee present count and employee absent count. In the
trip_member
table I stored
tripId
(foriegnkey),
empId
,
empPresentStatus
.
empPresentStatus= '1'
means he is absent,
empPresentStatus ='0'
means is present.


trip_details


tripId allocationId tripStatus

1 1 1
2 1 1



trip_member


id tripId empId empPresentStatus

1 1 G2E201 0
2 1 G2E202 0
3 1 G2E203 1
4 2 G2E204 0
5 2 G2E205 1


based on my table structure, how many employee present in the trip and how many employee absent in the trip, I want to take the count.


I tried this


$mysql = mysql_query("SELECT a.tripId, a.cabNo, COUNT('b.*') AS absentCount FROM trip_details a LEFT JOIN trip_member b ON a.tripId = b.tripId WHERE b.empPresentStatus = '1' AND a.tripStatus ='1' GROUP BY a.tripId");
while ($row = mysql_fetch_assoc($mysql)) {
$data[] = $row;
} // my requirement is based on tripId I want take the employee present count and emplyee absent count, in trip_member table I stored tripId (forienkey),empId,empPresentStatus.here come to know like empPresentStatus= '1' means he is absent, suppose empPresentStatus ='0' means is present.
$arrayName = array('status' => 'success', 'data' =>$data );
echo json_encode($arrayName);



Output I am getting


{
"status": "success",
"data": [
{
"tripId": "1",
"cabNo": "CBX100",
"absentCount": "1"
},
{
"tripId": "2",
"cabNo": "CBX101",
"absentCount": "1"
}
]
}


Up to now ok. I want to take the count how many employee are present in the trip. I don't how to take this count, if anyone know means update my answer.


Expected Results


{
"status": "success",
"data": [
{
"tripId": "1",
"cabNo": "CBX100",
"absentCount": "1",
"presentCount": "2"
},
{
"tripId": "2",
"cabNo": "CBX101",
"absentCount": "1",
"presentCount": "1"
}
]
}



Updated table (cab_allocation)


allocationId shiftTiming routeId cabId
1 1 1 CBX100
2 1 1 CBX101

Answer Source

You can use sub query in this.

Change your query with this one.

"SELECT a.tripId, a.cabNo, (select count(*) from trip_member as m WHERE m.tripId=a.tripId and m.empPresentStatus = '1') as presentcount,(select count(*) from trip_member as m WHERE m.tripId=a.tripId and m.empPresentStatus = '0') as absentcount FROM trip_details a WHERE a.tripStatus ='1' GROUP BY a.tripId"