Hello.
What do you think about this: SELECT a.name, COUNT(p.property_id) AS totalcount, SUM( IF(p.status = 'Active' AND p.approval = 'Active', 1, 0)) AS CCOUNT FROM accounts a, properties p WHERE a.account_id = p.account_id GROUP BY a.account_id; The data in tables: mysql> select * from accounts; +------------+------+ | account_id | name | +------------+------+ | 1 | a1 | | 2 | a2 | +------------+------+ mysql> select * from properties; +------------+-------------+------------+----------+ | account_id | property_id | status | approval | +------------+-------------+------------+----------+ | 1 | 1 | Active | Active | | 1 | 2 | Active | Active | | 1 | 3 | not-Active | Active | | 2 | 3 | not-Active | Active | +------------+-------------+------------+----------+ In the results of the query we see 2 'Active' properties which has a1, as it is in the properties table: mysql> SELECT a.name, COUNT(p.property_id) AS totalcount, SUM( IF(p.status = 'Active' AND p.approval = 'Active', 1, 0)) AS CCOUNT FROM accounts a, properties p WHERE a.account_id = p.account_id GROUP BY a.account_id; +------+------------+--------+ | name | totalcount | CCOUNT | +------+------------+--------+ | a1 | 3 | 2 | | a2 | 1 | 0 | +------+------------+--------+ Brian Dunning <[EMAIL PROTECTED]> wrote: > I'm searching a table of people who own properties, and I want to > also include the total count of related properties, and the count of > related properties whose (status is 'Active' and approval is > 'Active'). I've got: > > select accounts.name, count(properties.property_id) as totalcount > from accounts, properties where > accounts.account_id=properties.account_id group by accounts.account_id; > > Works fine. Now I just need to figure out how to add that second > count of property records meeting the two conditions. Anyone? > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]