CODEWITHSUNDEEP
postgresql
KTB
KTB

Reputation: 1529

PostgreSQL aggregate and filter

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

Upvotes: 1

Views: 6516

Answers (1)

zolamk
zolamk

Reputation: 6357

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

Upvotes: 3

Related Questions