CODEWITHSUNDEEP
javamysqlsqljdbc
Aich
Aich

Reputation: 1002

How can I rewrite this MySQL query for returning a count?

I have this query:

SELECT accounts.id
FROM accounts
  LEFT JOIN account_properties ON account_properties.account_id = accounts.id
GROUP BY accounts.id
HAVING COUNT(account_properties.id) = 0

It returns a list of all account IDs whose account does not have any account properties. How can I get the count of all accounts without properties?

SELECT COUNT(accounts.id)
FROM accounts
  LEFT JOIN account_properties ON account_properties.account_id = accounts.id
GROUP BY accounts.id
HAVING COUNT(account_properties.id) = 0

This does not work and only returns a list of 1s.

enter image description here

Upvotes: 2

Views: 80

Answers (3)

Tech Savant
Tech Savant

Reputation: 3766

    select count(*) from (SELECT accounts.id
    FROM accounts
    LEFT JOIN account_properties ON account_properties.account_id = accounts.id
    WHERE exists account_properties.id
    GROUP BY accounts.id)

Upvotes: 1

Uueerdo
Uueerdo

Reputation: 15941

SELECT `ap`.account_id IS NULL AS noProperties
   , COUNT(DISTINCT `a`.id) AS accountCount
FROM `accounts` AS `a`
  LEFT JOIN `account_properties` AS `ap` N `ap`.account_id = `a`.id
GROUP BY noProperties
HAVING noProperties
;

The GROUP BY gets the number of accounts with and without properties, and the HAVING filters the results so that only the count for no property accounts is in the final result.

Upvotes: 1

Arth
Arth

Reputation: 13110

You'll probably find these queries faster... assuming that id is a unique identifier of an account.

For the ids:

   SELECT accounts.id
     FROM accounts
LEFT JOIN account_properties ON account_properties.account_id = accounts.id
    WHERE account_properties.id IS NULL

For the count of ids:

   SELECT COUNT(*)
     FROM accounts
LEFT JOIN account_properties ON account_properties.account_id = accounts.id
    WHERE account_properties.id IS NULL

Upvotes: 3

Related Questions