Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

postgresql

I have three tables -

podcasts, videos and others.

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

Example for podcasts -

enter image description here

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

enter image description here enter image description here

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?

like image 297
krtkush Avatar asked Jan 24 '23 15:01

krtkush


2 Answers

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.

like image 27
coladict Avatar answered Jan 31 '23 04:01

coladict


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.

like image 52
forpas Avatar answered Jan 31 '23 05:01

forpas