Guides › COUNT vs COUNT DISTINCT

COUNT(*) vs COUNT(DISTINCT …) in SQL Server

“How many customers ordered last week?” is not the same question as “how many orders.” COUNT(*) answers the second. The first needs DISTINCT, or a count of customers.

Three numbers finance actually wants

SELECT COUNT(*) AS orders,
       COUNT(DISTINCT customer_id) AS customers,
       SUM(total_amount) AS revenue
FROM Orders
WHERE order_date >= DATEADD(day, -7, CAST(GETDATE() AS date))
  AND order_date <  CAST(GETDATE() AS date);

COUNT(*) counts rows. COUNT(customer_id) counts non-NULL ids (usually the same). COUNT(DISTINCT customer_id) counts people.

A JOIN then COUNT(*)

Join OrderLines and COUNT(*) becomes line items, not orders. If the grain changed, say so in the column name (AS lines), or count DISTINCT order_id.

COUNT(*) vs COUNT(column)

COUNT(*) includes rows where the column is NULL. COUNT(phone) skips NULL phones. That is how “customers” and “customers we can call” diverge.

Try it on Harbour & Co

Amy Nair (Finance Lead) needs this now: “Three numbers for shipped orders, all time: how many, total revenue, average order.” Write the query, run it, send the results. On the paid plan.

Take the ticket →

Learn it on a real ticket

Monday is free on WorkSQL. A colleague’s ticket lands, you write the T-SQL, we mark it. No videos. Five minutes.

Clock in free See Monday’s tickets