sql - Get "group by" parameter column name/ alias from another table

Question

I have a table podcast. Each podcast has category and subcategory associated to it in the form of ids.

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

Now, I want to get the count of podcasts under each category, subcategory combination -

SELECT podcast_category_id, podcast_subcategory_id, count(*)
FROM podcasts
where podcast_owner = 14 AND podcast_upload_time_stamp >= timestamp '2020-10-22 00:00:00'
GROUP BY podcast_category_id, podcast_subcategory_id

Output -

The output shows podcast_category_id and podcast_subcategory_id columns as expected. However, I want to replace podcast_category_id with category_name and podcast_subcategory_id with sub_category_name. How do I do that?

标签： sqlpostgresql

Answer 1

Join to those tables.

    SELECT c.category_name, sc.sub_category_name, count(p.*)
    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