SQL Server COUNT With JOIN and GROUP BY (HAVING, DISTINCT, LEFT JOIN)

Many guides still count joined rows with COUNT(*). Join a third table and those totals quietly inflate.

A safer sql server count join group by query joins the tables, groups by the column you report on, and counts the child key: SELECT c.State, COUNT(o.OrderID) FROM dbo.Customers AS c INNER JOIN dbo.Orders AS o ON o.CustomerID = c.CustomerID GROUP BY c.State;

Sample Tables for the COUNT, JOIN and GROUP BY Examples

Tested on SQL Server 2025 (17.0.1000.7) Developer edition. Ethan Walker has no orders; Hannah Brooks’s orders have several lines each.

CREATE TABLE dbo.Customers (
    CustomerID int PRIMARY KEY,
    Customer   nvarchar(16) NOT NULL,
    State      char(2) NOT NULL);
CREATE TABLE dbo.Orders (
    OrderID    int PRIMARY KEY,
    CustomerID int NOT NULL,
    OrderDate  date NOT NULL);
CREATE TABLE dbo.OrderLines (
    OrderID int NOT NULL,
    Product varchar(8) NOT NULL);

INSERT INTO dbo.Customers VALUES
(1, N'Hannah Brooks', 'WA'),     (2, N'Tyler Reed', 'TX'),
(3, N'Grace Miller', 'CA'),     (4, N'Marcus Bennett', 'TX'),
(5, N'Olivia Carter', 'WA'),   (6, N'Ethan Walker', 'CA');

INSERT INTO dbo.Orders VALUES
(101, 1, '2025-12-03'), (102, 1, '2026-02-14'), (103, 1, '2026-06-09'),
(104, 2, '2026-01-20'), (105, 2, '2026-07-04'),
(106, 3, '2025-11-28'), (107, 4, '2026-03-15'), (108, 5, '2026-08-22');

INSERT INTO dbo.OrderLines VALUES
(101, 'Mug'), (101, 'Tee'), (102, 'Mug'), (103, 'Cap'),
(103, 'Mug'), (103, 'Tee'), (104, 'Tee'), (105, 'Mug'),
(105, 'Cap'), (106, 'Cap'), (107, 'Tee'), (107, 'Hoodie'),
(108, 'Mug');

Method 1: COUNT With INNER JOIN and GROUP BY (Recommended)

Here’s the basic query, sorted by the count:

SET NOCOUNT ON;
SELECT c.State, COUNT(o.OrderID) AS Orders
FROM dbo.Customers AS c
INNER JOIN dbo.Orders AS o
    ON o.CustomerID = c.CustomerID
GROUP BY c.State
ORDER BY Orders DESC;

Output:

State Orders     
----- -----------
WA              4
TX              3
CA              1

How does this work? The join returns one row per order. GROUP BY collapses those eight rows into three states, and COUNT(o.OrderID) counts each.

Important: GROUP BY doesn’t sort or accept the Orders alias; ORDER BY does both. More on the INNER JOIN in SQL Server.

GROUP BY Multiple Columns: State and YEAR(OrderDate)

Same tables, now split by state and year with one of the SQL Server date functions. Group by every non-aggregated column:

SET NOCOUNT ON;
SELECT c.State, YEAR(o.OrderDate) AS OrderYear,
       COUNT(o.OrderID) AS Orders
FROM dbo.Customers AS c
INNER JOIN dbo.Orders AS o
    ON o.CustomerID = c.CustomerID
GROUP BY c.State, YEAR(o.OrderDate)
ORDER BY c.State, OrderYear;

Output:

State OrderYear   Orders     
----- ----------- -----------
CA           2025           1
TX           2026           3
WA           2025           1
WA           2026           3

How does this work? Each (State, year) pair is a group: WA splits into 1 + 3.

HAVING COUNT vs WHERE: Filter Groups After the Join

WHERE filters rows before grouping, as in an INNER JOIN with a WHERE clause; HAVING filters groups. Here: 2026 orders, then two or more:

SET NOCOUNT ON;
SELECT c.Customer, COUNT(o.OrderID) AS Orders2026
FROM dbo.Customers AS c
INNER JOIN dbo.Orders AS o
    ON o.CustomerID = c.CustomerID
WHERE o.OrderDate >= '2026-01-01'
GROUP BY c.Customer
HAVING COUNT(o.OrderID) >= 2
ORDER BY Orders2026 DESC;

Output:

Customer         Orders2026 
---------------- -----------
Hannah Brooks              2
Tyler Reed                 2

How does this work? WHERE drops Hannah’s 2025 order first, so she has 2, not 3, and HAVING removes everyone with one order.

Method 2: COUNT(DISTINCT) When the Join Fans Out Rows

Same orders, plus order lines. Each order now repeats once per line:

SET NOCOUNT ON;
SELECT c.Customer,
       COUNT(*)                  AS JoinedRows,
       COUNT(DISTINCT o.OrderID) AS Orders,
       COUNT(DISTINCT l.Product) AS Products
FROM dbo.Customers AS c
INNER JOIN dbo.Orders AS o     ON o.CustomerID = c.CustomerID
INNER JOIN dbo.OrderLines AS l ON l.OrderID = o.OrderID
GROUP BY c.Customer
ORDER BY c.Customer;

Output:

Customer         JoinedRows  Orders      Products   
---------------- ----------- ----------- -----------
Grace Miller               1           1           1
Hannah Brooks              6           3           3
Marcus Bennett             2           1           2
Olivia Carter              1           1           1
Tyler Reed                 3           2           3

How does this work? Hannah’s three orders have six lines, so COUNT(*) says 6, while COUNT(DISTINCT o.OrderID) counts unique keys and returns 3. It counts unique products too, across joins across multiple tables.

Method 3: LEFT JOIN With GROUP BY to Keep Zero-Count Rows

INNER JOIN drops Ethan. LEFT JOIN keeps him if you count a right-table column:

SET NOCOUNT ON;
SELECT c.Customer,
       COUNT(*)         AS CountStar,
       COUNT(o.OrderID) AS Orders
FROM dbo.Customers AS c
LEFT JOIN dbo.Orders AS o
    ON o.CustomerID = c.CustomerID
WHERE c.State = 'CA'
GROUP BY c.Customer;

Output:

Customer         CountStar   Orders     
---------------- ----------- -----------
Ethan Walker               1           0
Grace Miller               1           1

How does this work? Ethan gets one NULL-padded row: COUNT(*) counts it, COUNT(o.OrderID) skips the NULL. The LEFT JOIN with COUNT guide covers ON vs WHERE filters.

Fix Msg 8120: Column Not in the GROUP BY Clause

Select a column that’s neither grouped nor aggregated and it won’t compile. A plain TRY…CATCH missed it, so I used EXEC:

BEGIN TRY
    EXEC (N'SELECT c.State, c.Customer, COUNT(*)
            FROM dbo.Customers AS c GROUP BY c.State;');
END TRY
BEGIN CATCH
    PRINT CONCAT('Msg ', ERROR_NUMBER(), ': ', ERROR_MESSAGE());
END CATCH;

Output:

Msg 8120: Column 'dbo.Customers.Customer' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Add c.Customer to GROUP BY or wrap it in MAX(), then run it again to check your work.

Join Strings per Group With STRING_AGG

Same join, one string per customer: STRING_AGG(CAST(o.OrderID AS varchar(3)), ', ') WITHIN GROUP (ORDER BY o.OrderID) gave Hannah 101, 102, 103. It needs SQL Server 2017+.

Only WITHIN GROUP fixes the order. The GROUP BY join strings guide has more.

Is SELECT DISTINCT Needed With JOIN and GROUP BY?

No: SELECT DISTINCT COUNT(o.OrderID) gave me just 1, 2 and 3. DISTINCT merged result rows instead of deduping inside the count. Put it inside COUNT (more in DISTINCT in a join).

Which COUNT Method to Use?

You need	Use	Why
One count per group, matches only	INNER JOIN + COUNT(key)	Simplest and correct
Counts across 3+ tables	COUNT(DISTINCT key)	Join rows multiply
Groups with zero rows too	LEFT JOIN + COUNT(right key)	COUNT(*) returns 1

Things to Keep in Mind With COUNT, JOIN and GROUP BY

• Count the child key, not *, with a LEFT JOIN.
• COUNT(DISTINCT) can’t fix a SUM; pre-aggregate instead.
• Repeat YEAR(o.OrderDate) in GROUP BY; aliases don’t work there.
• Over 2,147,483,647 rows per group? Use COUNT_BIG, as for table row counts.

Next Step After GROUP BY

Next step: save your report query as a view in SQL Server. On PostgreSQL these queries run as is (PostgreSQL GROUP BY).

More reading:

• SQL Server STRING_AGG function
• SQL Server LEFT JOIN explained
• INNER JOIN vs LEFT JOIN

Frequently Asked Questions