MySQL count using join in 3 tables and condition in having clause-CodePudding

SELECT 
    COUNT( DISTINCT vendors.vendor_id ) AS numbers
FROM
    vendors
    
    INNER JOIN vendor_purchase ON
        vendor_purchase.vendor_id = vendors.vendor_id
    
    LEFT OUTER JOIN vendor_payments ON
        vendor_payments.vendor_purchase_id = vendor_purchase.vendor_purchase_id
    
WHERE
    vendors.shop_id = 3
    &&
    vendors.is_deleted = 0
    
GROUP BY
    vendors.vendor_id

HAVING
    IFNULL( SUM( vendor_payments.payment_amount ), 0 ) = 0

I am using this query to find the number of vendors which do not have any payments against them... but it gives me this:

I want a total count of vendors.

CodePudding user response：

Then using group by aggregate functions like count() apply to each group and not the complete result. Use a subquery:

SELECT COUNT(*)
FROM
(
    SELECT DISTINCT v.vendor_id
    FROM vendors v
    JOIN vendor_purchase pu ON pu.vendor_id = v.vendor_id
    LEFT JOIN vendor_payments pa ON pa.vendor_purchase_id = pu.vendor_purchase_id
    WHERE v.shop_id = 3
      AND v.is_deleted = 0    
    GROUP BY v.vendor_id
    HAVING SUM(pa.payment_amount) = 0
) tmp

CodePudding user response：

I think that it is unreadable code due to those confusing logic. Please refer to the below script.

SELECT
    COUNT( vendors.vendor_id ) AS numbers 
FROM
    (
    SELECT
        vpur.vendor_id 
    FROM
        vendor_purchase vpur
        LEFT JOIN vendor_payments vpay ON vpur.vendor_id = vpay.vendor_id 
        AND vpur.vendor_purchase_id = vpay.vendor_purchase_id 
    GROUP BY
        vpur.vendor_id 
    HAVING
        IFNULL( SUM( vpay.payment_amount ), 0 ) = 0 
    ) zeroPayment 
    INNER JOIN vendors ON 
    vendors.vendor_id = zeroPayment.vendor_id
WHERE
    vendors.shop_id = 3 AND vendors.is_deleted = 0