How to count related rows including subcategories?


I've got a few tables in a Postgres 12.3 database.

The first one is named category:

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| 6| |Important notes| 0|false | | |A | | 3|

The second table is called entry - which has no bearing on the question at hand.

And there is the link table category_entries_entry:

categoryId|entryId| ----------|-------| 4| 1| 5| 5| 5| 6| 4| 7| 3| 8| 6| 9|

A category can possess children, if parentCategoryId is not NULL then we're dealing with a child. For instance, the category with id = 5 is a subcategory of id = 4. Children can't have their own children, so only one level of nesting.

I need to count the number of entries for each category <em>including</em> subcategories.

This request does what I need, mostly. But it does not take into account the user:

SELECT COALESCE(c."parentCategoryId" , c.id) as cat_id , COUNT(*) as entries_in_cat FROM category c JOIN category_entries_entry r ON c.id = r."categoryId" WHERE c.is_base_template = false GROUP BY cat_id;


cat_id|entries_in_cat| ------|--------------| 4| 4| 3| 2|

The category table has also userId and the count should only be executed for a given user. Notably, only root categories have entries for userId.

And I want to list subcategories with their counts <em>additionally</em>. So the desired output with userId = 1 for the given sample is:

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

Here's a break down:

1) Category number 6 is a subcategory of the 3rd category it has 1 entry, so the result is correct.

2) Category number 3 is a category (that's to say, it does not have a parent), it contains 1 entry and another one should come from the 6th subcategory, that's 2 in total. Your script returns 1 which is wrong.

3) Category number 5 is a subcategory of the 4th category, it contains 2 entries. Your script returns also 2 which is right.

4) category number 4 is a category, it has 2 entries of its own and another two come from the 5th subcategory, that's 4 in total. Your script returns 2 which is wrong. It should return 4.

How can I achieve that?


This does the job for a <em>single</em> level of nesting:

To list only root categories, counts include subcategories:

WITH root AS ( SELECT id AS cat_id, id AS sub_id FROM category WHERE is_base_template = false AND "userId" = 1 ) SELECT c.cat_id, count(*)::int AS entries_in_cat FROM ( TABLE root UNION ALL SELECT r.cat_id, c.id FROM root r JOIN category c ON c."parentCategoryId" = r.cat_id ) c JOIN category_entries_entry e ON e."categoryId" = c.sub_id GROUP BY c.cat_id;

The point is to join on sub_id, but group by cat_id.

To list root categories like above, and subcategories <em>additionally</em>:

WITH root AS ( SELECT id AS cat_id, id AS sub_id FROM category WHERE is_base_template = false AND "userId" = 1 ) , ct AS ( SELECT c.cat_id, c.sub_id, count(*)::int AS ct FROM ( TABLE root UNION ALL SELECT r.cat_id, c.id AS sub_id FROM root r JOIN category c ON c."parentCategoryId" = r.cat_id ) c JOIN category_entries_entry e ON e."categoryId" = c.sub_id GROUP BY c.cat_id, c.sub_id ) SELECT cat_id, sum(ct)::int AS entries_in_cat FROM ct GROUP BY 1 UNION ALL SELECT sub_id, ct FROM ct WHERE cat_id <> sub_id;

<em>db<>fiddle here</em>

For an arbitrary number of nesting levels, use a recursive CTE. Example:

<ul><li>How to link rows in a many-to-many relationship where types have parents, grandparents etc</li> </ul>

About the optional short syntax TABLE parent:

<ul><li>Is there a shortcut for SELECT * FROM?</li> </ul>

Since the filtering applies to the parent categories only you'll need to apply it there first: then you can get the subcategories. Once you have the parent categories and the subcategories, you can join them to the entries and count them.

In sum, the query should look like:

with c as ( SELECT id FROM category WHERE userId = 1 AND is_base_template = false ), s as ( SELECT d.id FROM c JOIN category d on d.parentCategoryId = c.id ) SELECT u.id, count(*) as entries_in_cat FROM (select id from c union select id from s) u JOIN category_entries_entry r ON u.id = r."categoryId" GROUP BY u.id



  • string is not supported in calendar System.Globalization.GregorianCalendar
  • Any way to catch an exception occurring on a thread I don't own?
  • SQL query variables in MS Access
  • Python - How to get data types for all columns in CSV file?
  • HASKELL : Solving Towers of Hanoi
  • One to Many in Code First Entity Framework model - How do the relationships work?
  • Is it a good practice to mock Automapper in unit tests?
  • How to get service executable file path
  • Access AD users OneDrive for Businesses through Microsoft Graph in app-mode
  • kube-apiserver high CPU and requests
  • EF: one-to-one relationship
  • OWL Api, move class from parent A to B
  • Simple Donut Chart with Raphael
  • Failure to Read Updated AnyLogic DB Values
  • Speed up Date#parse & Date#strptime in Ruby, more elegant way or best practice?
  • Dynamic LINQ Multiple Where Clause
  • magento 2 rest api : get cart items with images
  • Apache same orgin request blocking
  • Display standard razor/mvc 3 validation messages displayed in another language
  • Newtonsoft.json serializing and deserializing base/inheirited where classes are from shared projects
  • Calling a flash ExternalInterface in swiffyobject
  • what do lines starting with double-slash '//' mean in a .npmrc?
  • Smarter Removing Unnecessary WhiteSpace CSV
  • Tensorflow crash using tf.train.Saver() with GPU
  • How to get a time and Date Separately?
  • Delete a ListItem by clicking the ImageView within the ListItem
  • how to add dashed border on highcharts “area” graph for every point
  • How to define a custom accuracy in Keras to ignore samples with a particular gold label?
  • What does “T extends Junk” mean in a generic class in Java?
  • Julia 1.0 UndefVarError - Scope of Variable
  • Copy and paste data from multiple workbooks to a worksheet in another Workbook
  • Unable to create Access token grant type in wso2 API manager store to test API
  • Error handeling in antlr 3.0
  • Print Longitude and Latitude in textbox Android
  • iphone image to video issue in video speed
  • WPF custom control and direct content support