Using COUNT with JOIN and GROUP BY in PostgreSQL

Question

I've got a few tables. The first one is named category, here it is:

id|template_id|name   |entry_count|is_base_template|can_rename|can_delete|section|userId|parentCategoryId|
--|-----------|-------|-----------|----------------|----------|----------|-------|------|----------------|
 1|           |Notes  |          0|true            |true      |true      |A      |      |                |
 2|           |ToDo   |          0|true            |true      |true      |A      |      |                |
 3|          1|Notes  |          0|false           |true      |true      |A      |     1|                |
 4|          2|ToDo   |          0|false           |true      |true      |A      |     1|                |
 5|           |Must Do|          0|false           |          |          |A      |      |               4|

The second table is called entry:

id|body                                         |title               |createdOn          |startDate|finishDate|isArchived|isLocked|isTrashed|
--|---------------------------------------------|--------------------|-------------------|---------|----------|----------|--------|---------|
 1|im a root entry                              |root entry          |2020-06-11 22:40:01|         |          |          |        |         |
 2|I must do it                                 |must do title       |2020-06-11 22:42:16|         |          |          |        |         |
 3|i was asked to do it right now and right here|one more must do    |2020-06-11 17:44:22|         |          |          |        |         |
 4|5 subcat body                                |5 subcat title      |2020-06-11 17:46:47|         |          |          |        |         |
 5|5 subcat body try 2                          |5 subcat title try 2|2020-06-11 17:51:26|         |          |          |        |         |
 6|5 subcat body try 3                          |5 subcat title try 3|2020-06-11 17:53:17|         |          |          |        |         |
 7|4 subcat body try 1                          |4 subcat title try 1|2020-06-11 17:54:34|         |          |          |        |         |

There's also the table of relations (category_entries_entry):

categoryId|entryId|
----------|-------|
         4|      1|
         5|      5|
         5|      6|
         4|      7|

A category can possess children, if the parentCategoryId is not NULL then we're dealing with its child. For instance, the fifth category (id = 5) is actually a subcategory of the forth category. One of the requirements at the moment is that children can't have their own children.

What I need is to count the number of entries on each category, regardless of how many subcategories a category has.

If I do this:

SELECT category.id as cat_id , COUNT(*) as entries_in_cat
FROM category
LEFT JOIN category_entries_entry
ON category.id = category_entries_entry."categoryId"
LEFT JOIN entry
ON entry.id =  category_entries_entry."entryId"
WHERE category.is_base_template = false
GROUP BY category.id;

Here is what I get:

cat_id|entries_in_cat|
------|--------------|
     5|             2|
     4|             2|
     3|             1|

The problem is that the category with id = 5 is a subcategory, which means it should be added to what is in the category with id = 4 cos that's her parentCategory id. So the table for this particular case should look like this:

cat_id|entries_in_cat|
------|--------------|
     4|             4|
     3|             1|

Hence the question: How do I achieve that?

oh right, the third table described is called `category_entries_entry`, I forgot to mention that, gotta correct it right quick. — Albert, Jun 12 '20 at 16:40

Gordon Linoff · Accepted Answer · 2020-06-12T19:29:04.237

1

If I understand correctly, you want to join category and relations and aggregate on the parent id if available:

SELECT COALESCE(c.parent_id, c.id) as cat_id , COUNT(*) as entries_in_cat
FROM category c JOIN
     relations r
     ON c.id = r.category_id
WHERE NOT c.is_base_template
GROUP BY cat_id;

edited Jun 12 '20 at 19:29

answered Jun 12 '20 at 16:38

Gordon Linoff

1,242,037
58
646
786

Thank you. Mmm not exactly. Well, first off, this query results in an empty set. I didn't get why you used `WHERE NOT`, if I remove `NOT` it seems to work as I'd like it to. – Albert Jun 12 '20 at 17:10
Oh my, I've just realised that I have to make the counting for a specific user. The category table , as you see, has the field called `userId`, so the query should contain something like `WHERE c.userId = USER_ID` and the USERID I will be taking from the token provided with a request. But I need this check. Would you please modify the query taking this into account. I'd greatly appreciate it. – Albert Jun 12 '20 at 17:37
I can ask that in another question if needed. What I need is the same result but for the USER with id = 1 (that's the only user in the DB at the moment) if I create another user, and execute this query for this newly created user, the query should return an empty set. – Albert Jun 12 '20 at 17:50
@Albert . . . I added the `not` and forgot to remove the `= false`. – Gordon Linoff Jun 12 '20 at 19:29
Aaah I see! I've actually asked another question...Would you please take a look? :) https://stackoverflow.com/questions/62350754/using-coalesce-with-join-and-group-by-in-postgresql – Albert Jun 12 '20 at 19:34

Using COUNT with JOIN and GROUP BY in PostgreSQL

1 Answers1