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

Question

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 1

You can use UNION ALL and add a constant to distinguish them.

SELECT 'podcasts' as "rowtype", c.category_name, sc.sub_category_name, count(p.*) AS type_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, 3

UNION ALL
SELECT 'videos' as "rowtype", c.category_name, sc.sub_category_name, count(o.*) AS type_count
FROM videolinks 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, 3

UNION ALL
SELECT 'other' as "rowtype", c.category_name, sc.sub_category_name, count(o.*) AS type_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, 3

I want to note that even just union might work, but union all is more often than not the one that gives the desired result.

Answer 2

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.