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]
