关于SQL COUNT(*)聚合函数的逻辑困惑——基于《Murach's SQL Server for Developers》的查询案例

Problem Description #

I'm working through Murach's SQL Server for Developers and wrote this summary query to group vendors by state (VendorState) and city (VendorCity), counting the number of invoices and calculating the average invoice amount for each group:

SELECT VendorState, VendorCity, COUNT(*) AS 'Invoice QTY', AVG(InvoiceTotal) AS 'InvoiceAvg'
FROM Invoices
JOIN Vendors ON Invoices.VendorID = Vendors.VendorID
GROUP BY VendorState, VendorCity
HAVING COUNT(*) >= 2
ORDER BY VendorState, VendorCity;

I understand most of the logic here, but I'm confused about the COUNT(*) aggregate function. The book says it counts the number of invoices per state/city group, but I thought it would count how many times each state/city appears in the Vendors table, not the number of invoices from the Invoices table. Can someone explain exactly what COUNT(*) is doing here?

Explanation #

Great question—this is a super common point of confusion when mixing joins and aggregate functions! Let's break it down clearly:

1. First, look at the joined result set
   When you join Invoices and Vendors on VendorID, you're creating a combined set where each row represents one invoice paired with its vendor's details. If a vendor has 4 invoices, that vendor's state and city will show up 4 times in this joined data—once for every invoice they've sent.

2. What COUNT(*) actually tracks
   COUNT(*) counts the total number of rows in each group after GROUP BY is applied. Since every row in the joined set maps to a single invoice, grouping by VendorState and VendorCity clusters all invoice rows tied to vendors in that location. COUNT(*) then tallies how many invoice rows are in each cluster—so it's exactly the number of invoices for that state/city group.

3. If you wanted to count vendors instead
   If your goal was to count unique vendors per state/city (not invoices), you'd use COUNT(DISTINCT Vendors.VendorID) instead. This would ignore duplicate vendor entries from multiple invoices and count each vendor only once per group.

4. Quick example to drive it home
   Let's say Austin, TX has two vendors: Vendor X has 3 invoices, Vendor Y has 2 invoices. The joined result set would have 5 rows for Austin, TX. After grouping, COUNT(*) would return 5 (total invoices), while COUNT(DISTINCT Vendors.VendorID) would return 2 (total unique vendors).

So the book is spot-on here—your query's COUNT(*) is definitely counting invoices per state/city group, not the number of vendors.

内容的提问来源于stack exchange，提问作者Leo