SQL Server Count Join Group By

In this SQL Server tutorial, we will learn and understand how to use the SQL Server COUNT function with JOIN types and GROUP BY clauses on two tables by the query.

I recently got an update and started working again on Employee Leave Management System in SQL Server. And I got a requirement to use the COUNT function with JOIN types and GROUP BY clauses on two tables by the query.

Here are the subtopics that we are going to discuss in this tutorial:

• How to use SQL Server Count Join Group By
• Using SQL Server Count Join Group By And Order By
• Working of SQL Server Count Join Group By Having Count
• How to use SQL Server Count Join Group By Multiple Columns
• Using SQL Server Count Join Group By Unique Values
• Working on SQL Server Count Join Group By Year

SQL Server Count Join Group By

Based on a common column between two or more tables, a JOIN clause in SQL Server is used to merge rows from those tables.

Let’s take the following query as an example of how to utilize the SQL Server COUNT function with the JOIN and GROUP BY clauses on two tables:

SELECT COUNT(USERS.FULL_NAME) AS TOTAL_COUNT,
COUNTRIES.NAME
FROM USERS
INNER JOIN COUNTRIES
ON COUNTRIES.CODE=USERS.COUNTRY_CODE
GROUP BY COUNTRIES.NAME;

With the help of the INNER JOIN clause, the SELECT statement returns the total count of the user from Australia and the United State of America countries and groups them based on the NAME column from the COUNTRIES table.

We hope that you have understood how to use the SQL Server COUNT function with INNER JOIN and GROUP BY clauses on two tables by the query.

Read: SQL Server OUTER JOIN

SQL Server Count Join Group By And Order By

Let’s see an example of SQL Server COUNT function with JOIN, GROUP BY, and ORDER BY clauses on two tables by the query.

Here are two tables i.e.; COUNTRIES and USERS table which will be used for SQL Server COUNT function with JOIN and GROUP BY, ORDER BY clauses:

EXAMPLE:

SELECT COUNT(COUNTRIES.NAME) AS TOTAL_COUNT,
USERS.FULL_NAME
FROM COUNTRIES
INNER JOIN USERS
ON COUNTRIES.CODE=USERS.COUNTRY_CODE
GROUP BY USERS.FULL_NAME
ORDER BY USERS.FULL_NAME DESC;

The SELECT statement counts the nation names based on the user’s name and also sorts them in descending order for the resultset with the aid of the INNER JOIN clause.

We hope that you have understood how to use SQL Server JOIN clause with COUNT, GROUP BY and ORDER BY clauses on two tables by the query.

Read: SQL Server Trigger Update with Examples

SQL Server Count Join Group By Having Count

In this subtopic section, we will see how to use the SQL Server COUNT function with the INNER JOIN clause and GROUP BY with HAVING clauses on two tables by the query.

Here are two tables i.e.; MERCHANTS and PRODUCTS used for the COUNT function with the JOIN clause and using the GROUP BY with the HAVING clause given below:

EXAMPLE:

SELECT COUNT(MERCHANTS.MERCHANT_NAME) AS TOTAL_MERCHANT_PER_PRODUCT,
PRODUCTS.MERCHANT_ID
FROM MERCHANTS
INNER JOIN PRODUCTS
ON MERCHANTS.ID=PRODUCTS.MERCHANT_ID
GROUP BY PRODUCTS.MERCHANT_ID
HAVING PRODUCTS.MERCHANT_ID>2;

With the help of the SELECT statement, we have counted the merchant’s name from the MERCHANTS table based on the merchant’s id from the PRODUCTS table.

And for that, we have grouped the records of the merchant id and checking the merchant id of the record is greater than 2 from the PRODUCTS table.

We hope that you have understood how to use the SQL Server COUNT function with the INNER JOIN clause and with GROUP BY and HAVING clauses on two tables by the query.

Read: Disable Trigger in SQL Server

SQL Server Count Join Group By Multiple Columns

The following query joins and groups by various table columns using the SQL Server COUNT function.

Here are two tables i.e.; COUNTRIES and MERCHANTS table used for the SQL Server COUNT function with JOIN and also use the GROUP BY clause on multiple columns of tables:

EXAMPLE:

SELECT COUNT(COUNTRIES.NAME) AS TOTAL_COUNT,
MERCHANTS.MERCHANT_NAME,MERCHANTS.COUNTRY_CODE
FROM COUNTRIES
INNER JOIN MERCHANTS
ON COUNTRIES.CODE=MERCHANTS.COUNTRY_CODE
GROUP BY MERCHANTS.MERCHANT_NAME, 
MERCHANTS.COUNTRY_CODE;

With the help of the INNER JOIN clause, we have counted the country’s name based on the merchant name and country code records in the result set. And it is grouped based on merchant name and country code for the result set.

We hope that you have understood how to use the SQL Server COUNT function with INNER JOIN and GROUP BY clauses on multiple columns of tables by the query.

Read: SQL Server Trigger Before Update

SQL Server Count Join Group By Unique Values

Let’s see an example of how to use SQL Server COUNT DISTINCT function with JOIN and GROUP BY clauses on two tables in the query.

Here are the two tables that were utilized for the JOIN clause: EMPLOYEES and MANAGERS:

EXAMPLE:

SELECT COUNT(DISTINCT MANAGERS.MANAGER_NAME) AS MANAGER_PER_EMPLOYEE,
EMPLOYEES.FIRST_NAME
FROM MANAGERS
LEFT JOIN EMPLOYEES
ON MANAGERS.MANAGER_ID=EMPLOYEES.MANAGER_ID
GROUP BY EMPLOYEES.FIRST_NAME;

With the help of the LEFT JOIN clause, we have counted the unique manager’s name based on the employee’s first name. And to get the manager’s name counted, we have grouped the employee’s first name from the EMPLOYEES table.

In the beginning, we used an ALIAS clause on the COUNT function for the output column name and gave the name MANAGER_PER_EMPLOYEE in the result set.

We hope that you have understood how to use SQL Server COUNT DISTINCT functions with JOIN and GROUP BY clauses on two tables in the query.

Read: If Else In Trigger SQL Server

SQL Server Count Join Group By Year

In this subtopic, we will see how to use SQL Server COUNT and YEAR functions with JOIN and GROUP BY clauses on two tables, as shown in the query.

USERS and COUNTRIES tables are used for the following query:

EXAMPLE:

SELECT COUNT(USERS.DATE_OF_BIRTH) AS TOTAL_COUNT,
COUNTRIES.NAME
FROM USERS
INNER JOIN COUNTRIES
ON USERS.COUNTRY_CODE=COUNTRIES.CODE
GROUP BY COUNTRIES.NAME;

The SELECT statement uses the INNER JOIN clause to count the user’s date of birth based on the name of the nation from both tables and group them based on the name of the country in the result set.

We trust that you now fully understand how to use the query’s two tables using the SQL Server COUNT YEAR functions with INNER JOIN and GROUP BY clauses.

Also, take a look at some more SQL Server tutorials.

• How to execute Trigger in SQL Server
•  to use Distinct in JOIN in SQL Server
• SQL Server Trigger On View
• SQL Server FULL OUTER JOIN with WHERE clause
• SQL Server Trigger to Increment Id

We now know about the post “SQL Server Count Join Group By “ after reading this tutorial. To assist you in comprehending the idea, we explored the following subtopics in detail.

• How to use SQL Server Count Join Group By
• Using SQL Server Count Join Group By And Order By
• Working of SQL Server Count Join Group By Having Count
• How to use SQL Server Count Join Group By Multiple Columns
• Using SQL Server Count Join Group By Unique Values
• Working on SQL Server Count Join Group By Year