PostgreSQL aggregate and filter

Question

I have a table which like below:

PROPERTY_ID OWNER_NAME  ISFORSALE   ISFORECLOSURE   ISFORRENT   CITY
    100         AA          1           0               1       abc
    101         AA          1           0               0       xyz
    102         BB          0           1               1       abc
    103         BB          1           0               0       abc
    104         BB          0           0               1       abc
    105         BB          1           0               1       abc
    106         CC          1           0               0       xyz

I am performing a summary query like this:

SELECT 
    COUNT(*) FILTER (WHERE ISFORSALE = 1) AS SALE_CNT,
    COUNT(*) FILTER (WHERE ISFORECLOSURE = 1) AS FORECLOSURE_CNT,
    COUNT(*) FILTER (WHERE ISFORRENT = 1) AS RENT_CNT
FROM property_table

further I need to add another field to this query which does the following - number of owners having more than two properties for sale:

SELECT count(*) from (
SELECT OWNER_NAME, COUNT(*) AS cnt1
FROM property_table 
WHERE ISFORSALE = 1
GROUP BY OWNER_NAME
HAVING COUNT(*) > 1
) t;

Is there a way I can add this query to the summary query rather than running a separate query, so the results would look like below?

SALE_CNT | FORECLOSURE_CNT | RENT_CNT | MUTI_PROP_OWNERS
________________________________________________________    
    5    |       1         |    4     |      2  

SQL to create the table and add data: https://ufile.io/u6zfbmf9

klin · Accepted Answer · 2021-09-21 10:49:21Z

3

You can use a sub query for this

SELECT 
    COUNT(*) FILTER (WHERE ISFORSALE = 1) AS SALE_CNT,
    COUNT(*) FILTER (WHERE ISFORECLOSURE = 1) AS FORECLOSURE_CNT,
    COUNT(*) FILTER (WHERE ISFORRENT = 1) AS RENT_CNT,
    (
        SELECT count(*) 
        FROM (
            SELECT OWNER_NAME, COUNT(*) AS cnt1 
            FROM property_table
            WHERE ISFORSALE = 1 
            GROUP BY OWNER_NAME 
            HAVING COUNT(*) > 1
        ) AS t
    ) AS MUTI_PROP_OWNERS
FROM property_table

here is a working example https://www.db-fiddle.com/f/wekoPwDqt3dVkRR4vmAbBi/0

edited Sep 21, 2021 at 10:49

klin

123k15 gold badges240 silver badges262 bronze badges

answered Sep 21, 2021 at 5:43

zolamk

6,58710 gold badges36 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

KTB Over a year ago

@Zola, but in case I have to add a where clause for the property_table, the subquery will have to do the same filtering right, which means effectively the same filtering runs two times?

zolamk Over a year ago

@klin, of course not, not sure why i formatted it that way

klin Over a year ago

@KTB - The query must perform two scans of the table. Even if we could find a way to use the result of the second scan (WHERE ISFORSALE = 1), the first would still be necessary to aggregate the other two columns.

Collectives™ on Stack Overflow

PostgreSQL aggregate and filter

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related