CODEWITHSUNDEEP
mysql
santa
santa

Reputation: 12512

Counts from two joined tables are showing the same even though they should not be

I have a pretty straight forward query that selects couple business name and counts results from other two joined tables. For some reason I get the same number of offers as rules, even though the number is only correct for rule.

For example I have 2 offers and 6 rules. Returned results show both as 6.

What am I missing?

SELECT
    business_profile.busID,
    business_profile.busName,
    COUNT(business_offers.ofr_id) AS cntOffers,
    COUNT(business_rules.rule_id) AS cntRules
FROM business_profile
LEFT JOIN business_offers ON (business_offers.ofr_busID = business_profile.busID)
LEFT JOIN business_rules ON (business_rules.rule_busID = business_profile.busID)
WHERE business_profile.busID > 1
GROUP BY business_profile.busID
ORDER BY cntRules DESC, cntOffers DESC 
LIMIT 20

Upvotes: 0

Views: 16

Answers (1)

Barmar
Barmar

Reputation: 782148

Use DISTINCT so you don't count the same ID multiple times.

SELECT
    business_profile.busID,
    business_profile.busName,
    COUNT(DISTINCT business_offers.ofr_id) AS cntOffers,
    COUNT(DISTINCT business_rules.rule_id) AS cntRules

Upvotes: 3

Related Questions