Join With Group By in SQL Server

In this SQL Server tutorial, we will learn and understand how to use the SQL Server GROUP BY clause with JOIN type on tables by the query.

Recently, I received an update from the client regarding the Country’s Geography database. In this database, I need to use various types of JOIN clauses with Group By clauses on the query tables. In the GROUP BY clause, we must use the aggregate function otherwise we will encounter a syntax error.

Here are the subtopics that we are going to cover:

• How to use SQL Server Join With Group By
• Using SQL Server Left Join With Group By
• How to use SQL Server Group By Join Strings
• How to use SQL Server Join With Group By Distinct
• How to use SQL Server Join With Group By Having Count
• Using SQL Server Join With Group By Where Clause

SQL Server Join With Group By

In SQL Server, the JOIN indicates how it should use the data from one table and the selected rows from another table. Let’s see an example of the SQL Server RIGHT JOIN clause with GROUP BY clause on tables in the following query:

EXAMPLE:

SELECT COUNT(STATES.STATE_NAME) AS STATE_PERCOUNTRY,
COUNTRIES.COUNTRY_NAME
FROM STATES
RIGHT JOIN COUNTRIES
ON STATES.COUNTRY_ID=COUNTRIES.COUNTRY_ID
GROUP BY COUNTRIES.COUNTRY_NAME;

In the query example with the help of the RIGHT JOIN clause, we have counted state name per country’s name from both tables i.e.; STATES and COUNTRIES table.

We hope that you have understood how to use the JOIN with the GROUP BY clause on tables by the query.

Read: SQL Server OUTER JOIN

SQL Server Left Join With Group By

Here we will see an example of SQL Server LEFT JOIN clause with the GROUP BY clause on tables in the following query:

EXAMPLE:

SELECT COUNT(CITY.CITY_NAME) AS CITY_PERSTATE,
STATES.STATE_NAME
FROM CITY
LEFT JOIN STATES
ON CITY.STATE_ID=STATES.STATE_ID
GROUP BY STATES.STATE_NAME;

With the help of the LEFT JOIN clause, the SELECT statement retrieves the count of the city name per state names from both tables i.e.; CITY and STATES table.

By using the COUNT function on the CITY_NAME column and the LEFT JOIN clause on the STATES table, we group the records in the STATE_NAME column by the STATE_ID field.

We hope that you have understood the concept of how to use the LEFT JOIN with the GROUP BY clause on tables by the query.

Read: What is SQL Server Cross Join?

SQL Server Group By Join Strings

Here, we will illustrate an example of SQL Server JOIN clause with the STRING_AGG function and GROUP BY clause on tables in the following query:

EXAMPLE:

SELECT STRING_AGG(STATES.STATE_NAME,', ') AS STRING_JOINING,
COUNT(STATES.STATE_NAME) AS STATES_PER_COUNTRY,
COUNTRIES.COUNTRY_NAME
FROM STATES
RIGHT JOIN COUNTRIES
ON STATES.COUNTRY_ID=COUNTRIES.COUNTRY_ID
GROUP BY COUNTRIES.COUNTRY_NAME
ORDER BY STATES_PER_COUNTRY DESC;

Please be aware that using the aggregate function as the STRING_AGG function inside the COUNT function will result in an error before we discuss the query notion. Because it didn’t adhere to the SQL Server rules, an error occurred.

With the help of the RIGHT JOIN clause in the SELECT statement, we have concatenated the records of the STATE_NAME column by using the comma [ , ] sign and also counted the state_name per country’s name from both tables i.e.; STATES and COUNTRIES tables.

By grouping the entries from the COUNTRY_NAME column in the COUNTRIES table, we were ultimately able to arrange the COUNT function records for the result set in descending order.

We trust that you now fully grasp how to use the STRING_AGG function and GROUP BY clause on the query’s tables in conjunction with the JOIN clause in SQL Server.

Read: SQL Server Outer Join With Count

SQL Server Join With Group By Distinct

Here, we will examine an illustration of the SQL Server DISTINCT COUNT function using a JOIN on tables:

EXAMPLE:

SELECT DISTINCT COUNT(CITY.CITY_NAME) AS CITY_PERCOUNTRY,
COUNTRIES.COUNTRY_NAME
FROM CITY
RIGHT JOIN COUNTRIES
ON CITY.COUNTRY_ID=COUNTRIES.COUNTRY_ID
GROUP BY COUNTRIES.COUNTRY_NAME;

In the RIGHT JOIN query example, we retrieved a unique count of city names per country with names from both tables i.e.; CITY and COUNTRIES tables.

We trust you are now aware of how to use the SQL Server RIGHT JOIN with the GROUP BY clause on tables via the query.

Read: How To Update Table Using JOIN in SQL Server

SQL Server Join With Group By Having Count

In the following query, we’ll see how SQL Server combines a JOIN clause with a GROUP BY HAVING clause on tables:

EXAMPLE:

SELECT COUNT(CITY.CITY_NAME) AS CITY_PERCOUNTRY,
STATES.STATE_NAME,
COUNTRIES.COUNTRY_NAME
FROM CITY
RIGHT JOIN STATES
ON CITY.STATE_ID=STATES.STATE_ID
RIGHT JOIN COUNTRIES
ON COUNTRIES.COUNTRY_ID=STATES.COUNTRY_ID
GROUP BY STATES.STATE_NAME,
COUNTRIES.COUNTRY_NAME
HAVING COUNT(CITY.CITY_NAME) >2;

With the help of the RIGHT JOIN on the SELECT statement, we retrieved the count of city names based on the state name and country name from these tables i.e.; CITY, STATES, and COUNTRIES tables.

Our final step was to group records of the state name and country name column based on the number of counts the city name exceeded 2.

We trust that you now fully grasp how to use the RIGHT JOIN clause in the query with the GROUP BY HAVING clauses on the tables.

Read: SQL Server Trigger On View

SQL Server Join With Group By Where Clause

The following query illustrates how to utilise the SQL Server RIGHT JOIN with the WHERE condition and GROUP BY clause on tables:

EXAMPLE:

SELECT DISTINCT COUNT(CITY.CITY_NAME) AS CITY_PERCOUNTRY,
COUNTRIES.COUNTRY_NAME
FROM CITY
RIGHT JOIN COUNTRIES
ON CITY.COUNTRY_ID=COUNTRIES.COUNTRY_ID
WHERE COUNTRIES.CONTIENT='North America'
GROUP BY COUNTRIES.COUNTRY_NAME;

With the help of the RIGHT JOIN clause, the SELECT statement retrieves a unique count of city names based on the country’s name from both tables i.e.; the CITY and COUNTRIES tables.

By using the WHERE clause, we were able to filter the data in the COUNTRIES table that contained information on the name of the continent North America.

We believe you now fully grasp how to use the SQL Server RIGHT JOIN clause in conjunction with the WHERE condition and the GROUP BY clause on tables by the query.

You may also like to read the following SQL Server tutorials.

• Trigger to insert data in another table in SQL Server
• SQL Server Trigger on Delete Insert Into Another Table
• SQL Server Update Trigger Only If Column is Modified

We now know about the post “Join With Group By in SQL Server” after reading this tutorial. Here is a list of each subtopic we covered to help you understand the concept.

• How to use SQL Server Join With Group By
• Using SQL Server Left Join With Group By
• How to use SQL Server Group By Join Strings
• How to use SQL Server Join With Group By Distinct
• How to use SQL Server Join With Group By Having Count
• Using SQL Server Join With Group By Where Clause