Question: How can I count distinct non-null values without writing COUNT(DISTINCT ...)?
Answer: Group the non-null values, then count the resulting groups. We asked this in an interview with ten candidates; only one gave the intended answer, and several first read it as an ordinary row-count question. That distinction is the whole puzzle. COUNT(InvoiceID) counts non-null rows, including repeated invoice IDs. It does not count the different invoice IDs.
For comparison, the direct answer when DISTINCT is allowed is:
SELECT COUNT(DISTINCT InvoiceID) AS TotalInvoiceIDs
FROM WideWorldImporters.Sales.InvoiceLines;Now answer the actual question. First group the non-null invoice IDs, then count the groups in an outer query. The inner query has one row per value; the outer query counts those rows. This is the form I would use when I want a single result without sending the whole grouped list to the client.
SELECT COUNT(*) AS TotalInvoiceIDs
FROM
(
SELECT InvoiceID
FROM WideWorldImporters.Sales.InvoiceLines
WHERE InvoiceID IS NOT NULL
GROUP BY InvoiceID
) AS InvoiceGroups;My original answer used a second, slightly playful SQL Server trick: return the grouped rows and read @@ROWCOUNT immediately afterward. I am keeping it because it is the interview idea that made the post memorable. It is better as a demonstration than as a production count query, since it returns every invoice ID before reporting the number.
SELECT InvoiceID
FROM WideWorldImporters.Sales.InvoiceLines
WHERE InvoiceID IS NOT NULL
GROUP BY InvoiceID;
SELECT @@ROWCOUNT AS TotalInvoiceIDs;The second statement must follow the grouped SELECT immediately because another statement can change @@ROWCOUNT. Filtering out nulls also matters: GROUP BY forms one null group, while COUNT(DISTINCT InvoiceID) ignores nulls.
I have more examples on SET NOCOUNT and @@ROWCOUNT, table row counts, and a short row-and-index-count video. If you have another readable way to answer the puzzle, please share it in the comments and I will credit you. For another inventory example, see finding the largest tables by row count.
Want to try this yourself? Here is how to install WideWorldImporters.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
23 Comments. Leave new
You can also UNION the table with itself to eliminate duplicates.
And of course you could use a temporary table and a cursor.
Nice.
CTE with Row_Number() function was the first way that comes to mind as you are looking for the distinct count. Row_Num will increase if there are more than one invoices, so selecting where the count is 1 will return all distinct invoices.
;WITH test_distinct
AS (
SELECT invoiceid
,ROW_NUMBER() OVER (
PARTITION BY invoiceid ORDER BY id
) AS row_num
FROM [WideWorldImporters].[Sales].[InvoiceLines]
)
SELECT count(invoiceid)
FROM test_distinct
WHERE row_num = 1;
hmm… nice idea. Thanks for sharing.
select sum(x) from (select 1 as x from [WideWorldImporters].[Sales].[InvoiceLines] group by invoiceid) as A
That would seem to be the simpliest that use just standard SQL.
Sum (Select 1)… I am impressed.
Original I had it as “select sum(1) from (select null ….” but decided to go more conventional.
An interesting hypothetical, but why would one ever choose to avoid COUNT DISTINCT? Not for performance reasons, surely.
You never know interviewer’s mental state.
Remember, in a job interview, you are also interviewing the people and company you are going to work for…. (several times I’ve walked away).
Using DENSE_RANK() Function.
select max(InvoiceRowNum) FROM
( select DENSE_RANK()OVER(order by InvoiceID) as InvoiceRowNum from [WideWorldImporters].[Sales].[InvoiceLines] ) Tbl
Wow! Nice.
What about:
Select object_name(spart.object_id) TableName, rows
from sys.partitions spart
Count Distinct excludes NULL values while counting whereas the solution provided in the post using @@ROWCOUNT includes NULL value.
SELECT
TOP 1 [Total InvoiceID] = ROW_NUMBER() OVER (ORDER BY InvoiceID)
FROM [WideWorldImporters].[Sales].[InvoiceLines]
WHERE InvoiceID IS NOT NULL
GROUP BY InvoiceID
ORDER BY 1 DESC
I have a doubt in the “Traditional Answer”
‘SELECT COUNT(InvoiceID) [Total InvoiceID]
FROM [WideWorldImporters].[Sales].[InvoiceLines]’
This would still return the total number of rows in the table right? how will it remove the duplicates?
Adding a ‘distinct’ before the ‘Count’ also returns the total number of records in the table, doesnt distinct. Can someone please explain this to me?
You are correct. That won’t work.
I assume that was a typo, and he intended either:
SELECT COUNT(distinct InvoiceID) [Total InvoiceID]
FROM [WideWorldImporters].[Sales].[InvoiceLines]
or
SELECT COUNT(InvoiceID) [Total InvoiceID]
FROM [WideWorldImporters].[Sales].[InvoiceLines]
GROUP BY InvoiceID
Create Table TestTable(i int)
go
insert into TestTable Values (1)
go
insert into TestTable Values (2)
go
insert into TestTable Values (2)
go
Select count(distinct i) from TestTable
go
Select distinct count(i) from TestTable
go
I am a little confused. You said distinct count of columns? Shouldn’t it be rows?
You are correct. It is confusing. Let me fix it.
Also the correct answer is incorrect? That will just give you the row count and not the distinct number of rows. You don’t need to select the @@rowcount because it is already showing in the bottom right hand corner.
Nevermind. I didn’t see the groupBy
if the total records in the table is more than 100 M then above solution will have performance issue.
i would suggest the below solution:
SELECT COUNT(1) FROM
(
SELECT InvoiceID
FROM [WideWorldImporters].[Sales].[InvoiceLines]
GROUP BY InvoiceID
) f