Multiple select statements in single query while using ‘group by’ and ‘join’

I have three tables –

podcasts, videos and others.

Each of the entities under these are associated with a category and subcategories.

Example for podcasts –

This category_id and subcategory_id have their name values in their respective tables –

Now, I want to get the count of podcasts, videos and text under each category & subcategory combination. My individual SQL query are these –

For podcasts –

SELECT c.category_name, sc.sub_category_name, count(p.*) AS podcast_count
FROM podcasts p
JOIN categories c ON c.category_id = p.podcast_category_id
JOIN sub_categories sc ON sc.sub_category_id = p.podcast_subcategory_id
WHERE p.podcast_owner = 14 AND p.podcast_upload_time_stamp >= timestamp '2020-10-22 00:00:00'
GROUP BY 1, 2

For others –

SELECT c.category_name, sc.sub_category_name, count(o.*) AS other_count
FROM otherlinks o
JOIN categories c ON c.category_id = o.other_link_category_id
JOIN sub_categories sc ON sc.sub_category_id = o.other_link_subcategory_id
WHERE o.other_link_owner = 14 AND o.other_link_add_time_stamp >= timestamp '2020-10-22 00:00:00'
GROUP BY 1, 2

And similar one for videos

Now, I want to combine them into a single query so that I get three columns for counts in a single result – podcast_count, other_count and videos_count. How do I do that?

Answer

Join the CROSS join of categories to sub_categories, so you get all the combinations of categories and subcategories, to the other 3 tables with LEFT joins and group by each combination and aggregate:

select c.category_name, sc.sub_category_name,
       count(distinct p.podcast_id) podcast_count,
       count(distinct v.video_id) videos_count,  
       count(distinct o.other_link_id) other_count 
from categories c cross join sub_categories sc
left join podcasts p on (p.podcast_category_id, p.podcast_subcategory_id) = (c.category_id, sc.sub_category_id)
  and p.podcast_owner = 14 AND p.podcast_upload_time_stamp >= timestamp '2020-10-22 00:00:00'
left join videos v on (v.video_category_id, v.video_subcategory_id) = (c.category_id, sc.sub_category_id)
  and v.video_owner = 14 AND v.video_upload_time_stamp >= timestamp '2020-10-22 00:00:00'
left join otherlinks o on (o.other_link_category_id, o.other_link_subcategory_id) = (c.category_id, sc.sub_category_id)
  and o.other_link_owner = 14 AND o.other_link_add_time_stamp >= timestamp '2020-10-22 00:00:00'
where coalesce(p.podcast_id, v.video_id, o.other_link_id) is not null
group by c.category_id, c.category_name, sc.sub_category_id, sc.sub_category_name

The WHERE clause filters out any combination of category and subcategory that does not contain any podcast, video or other link.