How do I count the rows returned for the unique ID within this sql query?

Question

This query returns all the rows that I will define as "recalls". See screenshot for results. I have tried and thus far failed to figure out how to sum the number of rows (each a distinct recall) per each id. In the results screenshot below there is only one id shown: 61401. But there are others IDs lower in the actual results. I'd like to count the number of rows(recalls) per unique id. I keep running into aggregate errors. I tried sub queries and 'group by' but just can't figure it out. Any ideas how I can do this? Ultimately I want two columns: id and recall_count.

SELECT 
prr_u.id, 
cprr_r.created_at AS recall_create_date, 
prr_m.created_at AS trial_start_date, 
prr_m.trial_days AS trial_period_length, 
prr_m.created_at + interval '1 day' * trial_period_length AS trial_end_date
FROM my_db.productionrr_users AS prr_u
INNER JOIN my_db.productionrr_memberships AS prr_m
ON prr_u.id = prr_m.id
INNER JOIN my_db.c4productionrr_recalls AS cprr_r
ON cprr_r.user_id = prr_m.id
WHERE recall_create_date BETWEEN trial_start_date AND trial_end_date;

Here is a snippet of the result:

I'd like to see results that look like this (where recalls_count is simply the number of rows returned per id):

I also tried Tan's suggestion here and get the aggregate DB Errorcode=500310.

SELECT
prr_u.id as id,
cprr_r.created_at AS recall_create_date, 
prr_m.created_at AS trial_start_date, 
prr_m.trial_days AS trial_period_length, 
prr_m.created_at + interval '1 day' * trial_period_length AS trial_end_date,
COUNT(DISTINCT recall_create_date) as recall_count 
FROM my_db.productionrr_users AS prr_u
INNER JOIN my_db.productionrr_memberships AS prr_m
ON prr_u.id = prr_m.id
INNER JOIN my_db.c4productionrr_recalls AS cprr_r
ON cprr_r.user_id = prr_m.id
WHERE recall_create_date BETWEEN trial_start_date AND trial_end_date
GROUP BY prr_u.id;

Also, I am able to count the total rows within the query but I still can't do it for each id:

SELECT COUNT(*) FROM (
SELECT prr_u.id, prr_u.email, prr_m.status, cprr_r.created_at AS recall_create_date, prr_m.created_at AS trial_start_date, prr_m.trial_days AS trial_period_length, prr_m.created_at + interval '1 day' * trial_period_length AS trial_end_date, prr_m.expires_at
FROM my_db.productionrr_users AS prr_u
INNER JOIN my_db.productionrr_memberships AS prr_m
ON prr_u.id = prr_m.id
INNER JOIN my_db.c4productionrr_recalls AS cprr_r
ON cprr_r.user_id = prr_m.id
WHERE recall_create_date BETWEEN trial_start_date AND trial_end_date
);

Output is the total count of:

@gmb I am unable to get past this aggregate function error. I have tried this as I cannot run your code as it is:

SELECT prr_u.id,
prr_m.created_at + interval '1 day' * prr_m.trial_days AS trial_end_date,
COUNT(*) AS recalls_count
FROM my_db.productionrr_users AS prr_u
INNER JOIN my_db.productionrr_memberships AS prr_m 
ON prr_u.id = prr_m.id
INNER JOIN my_db.c4productionrr_recalls AS cprr_r 
ON cprr_r.user_id = prr_m.id
WHERE cprr_r.created_at BETWEEN prr_m.created_at AND trial_end_date
GROUP BY prr_u.id;

which produces the following aggregate error:

`

Answer 1

It looks like you just want aggregation:

SELECT prr_u.id, COUNT(*) recalls_count
FROM my_db.productionrr_users AS prr_u
INNER JOIN my_db.productionrr_memberships AS prr_m ON prr_u.id = prr_m.id
INNER JOIN my_db.c4productionrr_recalls AS cprr_r ON cprr_r.user_id = prr_m.id
WHERE recall_create_date BETWEEN trial_start_date AND trial_end_date
GROUP BY ppr_u.id;

Answer 2

Select prr_u.id as id, count(distinct recall_create_date) as recall_count... Group by prr_u.id 

This code groups according to id then counts the number of records with that specific id. In the code where you grouped using all columns, each group consists of each recall entry instead of a group of recall entries under the same id.

edit: You seem to be getting the aggregate error because you cannot select additional columns without grouping by them (or using an aggregate function on them). See: Aggregate function error while using group by clause in SQL.

Answer 3

I figured out the answer. But I don't need to show the trial end date column but I need that column to identify the end_date. My issue all along was that I was adding a non unique column to my select and group by so every 'recall' was counted once and not aggragated.

SELECT prr_u.id,
prr_m.created_at + interval '1 day' * prr_m.trial_days AS trial_end_date,
COUNT(*) AS recalls_count
FROM my_db.productionrr_users AS prr_u
INNER JOIN my_db.productionrr_memberships AS prr_m 
ON prr_u.id = prr_m.id
INNER JOIN my_db.c4productionrr_recalls AS cprr_r 
ON cprr_r.user_id = prr_m.id
WHERE cprr_r.created_at BETWEEN prr_m.created_at AND trial_end_date
GROUP BY prr_u.id, trial_end_date;
```
[![enter image description here][1]][1]


  [1]: https://i.stack.imgur.com/JmzWg.png