Select count for total times foreign key exists in table with join-CodePudding

My table setup is as follows

Shortlist

id (primary key)	property_id(foreign key)	user_id
1	100	2
2	101	2
3	103	4
4	100	1
5	100	3
6	101	1

Property

id (primary key)	account_id
100	1
101	2
102	3
103	4

I then have the following query

SELECT DISTINCT `s`.`user_id`, `s`.`property_id`, `p`.`id`, `p`.`account_number`, COUNT(`s`.`property_id`) as `shortlistCount` 
FROM `shortlist` `s` 
INNER JOIN `property` `p` ON s.`property_id` = p.`id` 
WHERE s.`user_id` = "2" 
GROUP BY s.`property_id` 
LIMIT 10

I want to add a count field alias as shortlistCount that returns the total number of times each property_id field appears in the shortlist table. (or id field in the property table, whichever is easier in this context)

For example, the property_id of 100 appears 3 times in the shortlist table.

So the result should be

| user_id | property_id | id  | account_number | shortlistCount |
|---------|-------------|-----|----------------|----------------|
| 2       | 100         | 100 | 1              | 3              |
| 2       | 101         | 101 | 2              | 2              |

However, shortlistCount currently always returns 1

How can I can update my shortlistCount alias to return the above result?

CodePudding user response：

To count the property_ids you can use a correlated subquery:

SELECT s.user_id, s.property_id, p.id, p.account_number, 
 (select Count(*) from Shortlist s2 where s2.property_id = s.property_id) shortlistCount 
FROM shortlist s 
JOIN property p ON s.property_id = p.id 
WHERE s.user_id = 2
LIMIT 10;