Mysql nested count

Question

I have 3 tables connected to each other

booking > bid (booking_id) > bid_participants (bid_id)

so when a booking is created, it will be blasted and received to more than 0 person or not received at all

what i am trying to achieve is to count the booking if is it received by more than 0 person and consider it as 1

something like

SELECT 
    COUNT(b.id) as booking_count,
    COUNT( if(COUNT(bip.bid_status) > 0, 1, 0) ) as bid_received_count // this part
    DATE(b.date_created) AS daily_booking
FROM booking AS b
JOIN booking_route AS br on b.id = br.booking_id
JOIN bid AS bi on b.id = bi.booking_id
JOIN bid_participant AS bip on bi.id = bip.bid_id
WHERE 
DATE(b.date_created) BETWEEN '2015-06-04' AND '2015-06-10'
AND br.service_type = 1
AND bip.bid_status = 1
GROUP BY daily_booking;

this is as far as i can get

SELECT 
    COUNT(b.id) AS total_booking,
    COUNT(CASE WHEN bip.bid_status > 0 THEN 1 END) AS bid_received_count,
    br.service_type,
    DATE(b.date_created) AS daily_booking
FROM booking AS b
JOIN booking_route AS br on b.id = br.booking_id
JOIN bid AS bi on b.id = bi.booking_id
JOIN bid_participant AS bip on bi.id = bip.bid_id
WHERE 
DATE(b.date_created) BETWEEN '2015-06-04' AND '2015-06-10'
AND br.service_type = 1
AND bip.bid_status = 1
GROUP BY daily_booking;
AND br.service_type = 1
AND bip.bid_status = 1
group BY daily_booking;

with above's query, with booking count for 2015-06-04 is 7468, i get 9359 booking received, it should be no more than the booking count it self.

Answer 1

I think you need to do a different approach for what you want

Just select all the bookings between the date and count those in a subquery It should be something like this, but its a bit hard to test with the given information

SELECT 
    (SELECT count(*)
        FROM booking AS b
        JOIN booking_route AS br on b.id = br.booking_id
        JOIN bid AS bi on b.id = bi.booking_id
        JOIN bid_participant AS bip on bi.id = bip.bid_id
        WHERE bip.bid_status > 0 
            AND DATE(b.date_created) BETWEEN '2015-06-04' AND '2015-06-10'
            AND br.service_type = 1
            AND bip.bid_status = 1
        GROUP BY daily_booking;
            AND br.service_type = 1
            AND bip.bid_status = 1
            group BY daily_booking) AS bid_received_count