Get Total Count From Second Table - SQL Server

How to get count of a column from second table if there is any entry using SQL Server.

Hi, Today we will check that how we can get count or number of rows in a second table agains a column in first table. for example we have a table of products and we want to check total number of orders against a product. this can be done in various ways but we want to avoid sub query as mostly i don't prefer sub query due to efficiency of execution.

For this problem resolution we can use COMMON TABLE EXPRESSION. basically a common table expression (CTE) is a temporary result set. there are some limitations also but as above mentioned task we can use CTE which is not going to fall us in performance issue. As a comparison to temp table there is no need to worry about it, in CTE, as these have an execution scope within statement. CTE can also be used within Views.

Anyway Let's have a look practically: we want to get count for each product from order table but it should also be shown as column in normal query we don't want to use union or some other technique to combine the result set.

For example: normally we are showing mobile products in table and we get result from below query:

SELECT PROD_ID,PROD_TITLE 
    FROM PRODUCT_TABLE  
    WHERE CATEGORY_NAME = 'MOBILE'

and result show like this

but we want to show the count of each product as third column as shown below:

so we will use a common table expression to get this done very simple. below is shown common table expression:

;WITH CTE_PROD_COUNT (PROD_ID,TOTAL_COUNT) 
AS (
     SELECT PROD_ID,COUNT(*)TOTAL_COUNT 
      FROM ORDER_TABLE GROUP BY PROD_ID)

so how we will combine this query with our simple product query to get it done in a single result set. we will use CTE common table expression as table simple table and join it will our original query to get count for each table as below:

;WITH CTE_PROD_COUNT (PROD_ID,TOTAL_COUNT) 
  AS (
        SELECT PROD_ID,COUNT(*)TOTAL_COUNT 
          FROM ORDER_TABLE GROUP BY PROD_ID)
    
   SELECT P.PROD_ID,P.PROD_TITLE,CT.TOTAL_COUNT 
   FROM PRODUCT_TABLE P 
   LEFT OUTER JOIN CTE_PROD_COUNT CT ON CT.PRODID1 = P.PROD_ID
    WHERE P.CATEGORY_NAME = 'MOBILE'

With this query you can use your current filter and CTE will simple add a total count of each product in query as a column.
it will look like this:

Hopefully you like this experience. Comments and suggestions are always welcome!!