How to count unique records?

As I remember, some syntax is not allowed in this forum. I will add some "-" below.

I can use S-QL to pull data from MS S-QL database to excel sheet.

If I use the query S-ELECT ColumnO, ColumnP, ColumnQ, ColumnX, ColumnY, ColumnZ FROM Table_name WHERE ColumnO = 100 AND ColumnP = 10 AND ColumnQ = 30 , I will get something like below screenshot.

I know how to put the screenshot table into excel sheet, via VBA. But this is not the final output yet. From there, I will need to calculate # of unique records, the answer is 3. I highlight them in 3 different colors. All columns have same data.

Note: You may wonder why the output table (screen table) contains duplicate rows, because in the query, I only select 6 columns, and there are many other columns.

All I want to get is the output number 3 , how should I modify the query to include something like COUNT/DISTINCT ? So there is no need to import raw data into excel sheet. It is possible that output result is 0

Otherwise, I will run a VBA loop through ColumnX/ColumnY/ColumnZ and count the unique records.

Hi VAer,

It's been a while since I wrote anything in SQL but this worked in an Access Db I quickly created:

SELECT COUNT(*) AS Unique_Count FROM (SELECT DISTINCT ColumnO, ColumnP, ColumnQ, ColumnX, ColumnY, ColumnZ FROM Table_name) AS Temp;

Regards,

Robert

@Trebor76

Thanks, but it also needs to include this information WHERE ColumnO = 100 AND ColumnP = 10 AND ColumnQ = 30

Should I write it as
SELECT COUNT(*) AS Unique_Count FROM (SELECT DISTINCT ColumnO, ColumnP, ColumnQ, ColumnX, ColumnY, ColumnZ FROM Table_name WHERE ColumnO = 100 AND ColumnP = 10 AND ColumnQ = 30) AS Temp

Maybe I don't need Unique_Count and Temp, shall I?
SELECT COUNT(*) FROM (SELECT DISTINCT ColumnO, ColumnP, ColumnQ, ColumnX, ColumnY, ColumnZ FROM Table_name WHERE ColumnO = 100 AND ColumnP = 10 AND ColumnQ = 30)

In VBA, can I assign it to variable directly?

Dim NumOfRecord As Integer
NumOfRecord = SELECT COUNT(*) FROM (SELECT DISTINCT ColumnO, ColumnP, ColumnQ, ColumnX, ColumnY, ColumnZ FROM Table_name WHERE ColumnO = 100 AND ColumnP = 10 AND ColumnQ = 30)

Edit: here is more detailed code. How can I get the final output number? I have the SELECT statement, but how to pass the value to a variable NumOfRecord ?

Please Login or Register  to view this content.

Glad you got it sorted and thanks for the rep 😎