SELECT
    `realty`.`realty_id`,
    `realty`.`name`,
    `realty`.`square`,
    COUNT(co.`direction`) OB, /*исх*/
    COUNT(ci.`direction`) IB, /*вход*/
    COUNT(me.`status`) HE,
    COUNT(ml.`status`) PL,
    COUNT(mh.`status`) NH
 
FROM
    `realty`
LEFT JOIN
    `calls` co
ON
    `realty`.`id` = co.`realty_id`
    AND co.`direction` = 'Outbound' /*исх*/
    AND co.`deleted` = 0
 
LEFT JOIN
    `calls` ci
ON
    `realty`.`id` = ci.`realty_id`
    AND ci.`direction` = 'Inbound' /*вход*/
    AND ci.`deleted` = 0
 
LEFT JOIN
    `meetings` me
ON
    `realty`.`id` = me.`realty_id`
    AND me.`status` = 'Held'
    AND me.`deleted` = 0
 
LEFT JOIN
    `meetings` ml
ON
    `realty`.`id` = ml.`realty_id`
    AND ml.`status` = 'Planned'
    AND ml.`deleted` = 0
 
LEFT JOIN
    `meetings` mh
ON
    `realty`.`id` = mh.`realty_id`
    AND mh.`status` = 'Not Held'
    AND mh.`deleted` = 0
 
WHERE
    `building_id` = '".$buildings->id."'
    AND
    `realty`.`deleted` = 0
GROUP BY `realty`.`realty_id`;

SELECT
	realty.id,
    count(case when meetings.status = 'Held'  then meetings.status end) as held,
    count(case when meetings.status = 'Not Held'  then meetings.status end) as notHeld,
    count(case when meetings.status = 'Planned'  then meetings.status end) as planned
FROM
 	realty
INNER JOIN
	meetings ON meetings.realty_id = realty.id
INNER JOIN
	buildings ON buildings.id =realty.building_id
WHERE
	meetings.deleted <> 1
    AND
    buildings.id = 'ad2010fc-cd64-45e2-1efb-56540a4811ac'
GROUP BY realty.id

SELECT
    realty.id,
    count(case when calls.direction = 'Outbound'  then calls.direction end) as outbound,
    count(case when calls.direction = 'Inbound'  then calls.direction end) as inbound,
    count(case when meetings.status = 'Held'  then meetings.status end) as held,
    count(case when meetings.status = 'Not Held'  then meetings.status end) as notHeld,
    count(case when meetings.status = 'Planned'  then meetings.status end) as planned
FROM
 	realty
INNER JOIN
	calls ON calls.realty_id = realty.id
INNER JOIN
	meetings ON meetings.realty_id = realty.id
INNER JOIN
	buildings ON buildings.id =realty.building_id
WHERE
	meetings.deleted <> 1
    AND
    buildings.id = 'ad2010fc-cd64-45e2-1efb-56540a4811ac'
GROUP BY realty.id

SELECT
    realty.id, realty.realty_id, realty.square,
    COUNT(distinct CASE WHEN calls.direction = 'Outbound'  THEN calls.id END) AS outbound,
    COUNT(distinct CASE WHEN calls.direction = 'Inbound'  THEN calls.id END) AS inbound,
    COUNT(distinct CASE WHEN meetings.STATUS = 'Held'  THEN meetings.id END) AS held,
    COUNT(distinct CASE WHEN meetings.STATUS = 'Not Held'  THEN meetings.id END) AS notHeld,
    COUNT(distinct CASE WHEN meetings.STATUS = 'Planned'  THEN  meetings.id  END) AS planned
FROM
    realty
LEFT OUTER JOIN
    buildings ON buildings.id = realty.building_id
LEFT OUTER JOIN
    calls ON calls.realty_id = realty.id
LEFT OUTER JOIN
    meetings ON meetings.realty_id = realty.id
	AND
	meetings.deleted <> 1
WHERE
    buildings.id = 'ad2010fc-cd64-45e2-1efb-56540a4811ac'
GROUP BY realty.id