sql - Best way to get a count of each category type

Question

I have a query below which returns the product_id, product_name and category_name from two different tables.

SELECT p.product_id, 
       p.product_name, 
       c.category_name 
FROM   [BikeStore].[production].[products] p 
       JOIN [BikeStore].[production].[categories] c 
         ON p.category_id = c.category_id 
ORDER  BY c.category_name 

I want to add a fourth column next to each row showing how many of each categories are there.

I wrote the query for this like this below:

  SELECT p.product_id, 
       p.product_name, 
       c.category_name, 
       (SELECT Count(*) 
        FROM   [BikeStore].[production].[products] p1 
               JOIN [BikeStore].[production].[categories] c1 
                 ON p1.category_id = c1.category_id 
        WHERE  c1.category_id = c.category_id) AS totals 
FROM   [BikeStore].[production].[products] p 
       JOIN [BikeStore].[production].[categories] c 
         ON p.category_id = c.category_id 
ORDER  BY c.category_name 

I am not sure if this is the right approach. Could you please let me know if there is a better way to do this.

question from:https://stackoverflow.com/questions/65601636/best-way-to-get-a-count-of-each-category-type

Answer 1

You can use a window function:

SELECT p.product_id, 
       p.product_name, 
       c.category_name,
       COUNT(*) OVER (PARTITION BY c.category_name)
FROM [BikeStore].[production].[products] p JOIN
     [BikeStore].[production].[categories] c 
     ON p.category_id = c.category_id 
ORDER  BY c.category_name