CODEWITHSUNDEEP
sqlpostgresqlcountconditional-statements
Hommer Smith
Hommer Smith

Reputation: 27852

Get count of associated records with conditions

I have two tables:

users which have just two columns: id: INT, name: TEXT

houses which have three columns: id: INT, user_id: INT, sold_at: DATE

I have this query that founds the number of houses sold by a user:

SELECT users.id, COUNT(CASE WHEN houses.sold_at IS NOT NULL THEN 1 END)
FROM users
LEFT JOIN houses on houses.user_id = users.id
GROUP BY users.id

This works, but I am wondering if there is a way to not have the condition in the count. I have tried this:

SELECT users.id, COUNT(houses.id)
FROM users
LEFT JOIN houses on houses.user_id = users.id
WHERE houses.sold_at IS NOT NULL
GROUP BY users.id

But this doesn't work, because if a user does not have any associated house, it won't show up in the result set.

Is my only option to do conditions on the COUNTs?

Upvotes: 1

Views: 256

Answers (3)

ScaisEdge
ScaisEdge

Reputation: 133380

count (if i rememeber correctly) should work only on not null value so indicating the column name in the count() function you could use directly 

SELECT users.id, COUNT( houses.sold_at )
FROM users LEFT JOIN houses on houses.user_id = users.id
GROUP BY users.id

and you should not use left join column name in where otherwsie this becase as an inner join .. for this you should move the condition in the related ON clause

Upvotes: 1

Maxim
Maxim

Reputation: 2391

This should works with RIGHT JOIN

 SELECT users.id, COUNT(houses.id)
    FROM users
    RIGHT JOIN houses on houses.user_id = users.id
    WHERE houses.sold_at IS NOT NULL
    GROUP BY users.id

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

You can try by putting the condition of where clause in ON clause like below-

SELECT users.id, COUNT(houses.id)
    FROM users
    LEFT JOIN houses on houses.user_id = users.id
    and houses.sold_at IS NOT NULL
    GROUP BY users.id

Upvotes: 1

Related Questions