how to count unique records with sql

question

let’s say i have a table with one column containing the following 6 records:

3
3
4
5
5
6

is there a way with a single standard SQL query to count the unique records in that column? (the query should return “4″)

answer

the following sql works in MySQL, Oracle, and SQL Server:

SELECT COUNT(DISTINCT column_name)
FROM table_name

the following sql works where ever subqueries are accepted (like Microsoft Access):

SELECT COUNT(*)
FROM (SELECT DISTINCT column_name FROM table_name)
/* or */
SELECT COUNT(*)
FROM (SELECT column_name FROM table_name GROUP BY column_name)

NB: Access’s Query by Example (QBE) UI will transform the first query above into:

SELECT Count(*) AS Expr1
FROM [SELECT DISTINCT column_name FROM table_name]. AS [%$##@_Alias];

the %$##@_Alias is just some whacked out alias (i’m sure there’s a story behind that), but it could be anything. however the “.” (period) after the bracket closing the subquery, well, apparently that’s required. i’m not sure why.

thanks dad and tim!

Tuesday, June 22, 2004 2:44pm
Tech, Tip Jar

relatedposts

28 comments

name
blog (optional)
comment
Brian
6/22/04 9:28pm

Not really an answer, but given a file named z.txt containing the column one per line, then “cat < z.txt | uniq | wc -l” returns 4.

Brian
6/22/04 9:45pm

How about this… From:
http://www.w3schools.com/sql/func_count_distinct.asp.
The following example works with ORACLE and Microsoft SQL server but not with Microsoft Access.
SELECT COUNT(DISTINCT Company) FROM Orders

Tim
6/22/04 9:56pm

That’s a tricky one….the exact syntax might differ depending on the DBMS but this works in both MySQL and Oracle:

SELECT COUNT(DISTINCT column_name) FROM table_name;

Or if the DBMS supports subqueries you could go with the more readable:

SELECT COUNT(*) FROM (SELECT DISTINCT column_name FROM table_name)

matt
6/23/04 10:39am

everything has just flown right over my head. you programmers are all crazy! :)

Melanie
6/23/04 12:16pm

You need a separate commments space for your neatlinks.

justin
6/23/04 3:17pm

matthew: hehe.

melanie: done. i moved your comment over there.

Brian
6/23/04 8:28pm

OK, Mister smarty pants, I want my blog to go in reverse chronogical order, that is, from the earliest post to the latest post. I asked Blogger tech support and they don’t have it. Can you figure it out?

justin
6/23/04 11:30pm

you mean chronological order, right? your blog is already in reverse chronological order.

when you say blog… what do you mean? the five or so most recent posts? the entire archive? the posts in the monthly archives?

assuming you mean the posts in your monthly archive i’ve whipped up a little example that shows how you might reverse the posts

Woho
3/10/05 9:42am

You are my hero, i really needed that SQL statement :)

Jane
10/14/05 12:36am

When I use a SELECT DISTINCT query.
Eg SELECT DISTINCT Organisation FROM TBL_Orgs.

How do I select another another field but still keep the Organisation field distinct.
When I use SELECT DISTINCT Organisation, Branch FROM Tbl_Orgs. The organisation field is no longer distinct. I want to be able to select distinct organisations but still see the branch associated with that organisation.

justin
10/14/05 9:21am

Jane try this:

SELECT Organisation, Branch
FROM TBL_Orgs
GROUP BY Organisation, Branch

Abdul
12/8/05 7:44am

Anyone know how can i extract the unqiue records before i count how many exist?

justin
12/8/05 1:04pm

Abdul, I’m not sure exactly what you mean, but there are a number of options.

SELECT DISTINCT column_name
FROM table_name

should work, but I prefer:

SELECT column_name
FROM table_name
GROUP BY column_name

Muncho
1/28/06 1:19am

Neat!
Thanks!

Jason
3/3/06 7:13pm

My problem: I actually want to count uniques in two different fields. Do I tried Count (DISTINCT Field A, Field B) but this did not work.

Irina
10/25/06 1:46pm

How to ‘group by’ records if you have duplicate records with different dates but would like to see only one entry per record with the most recent date?
Thank you very much!

justin
11/13/06 3:45pm

Man, I constantly refer back to this blog post.

Michael
1/9/07 8:46am

This is a good way to do it. Worked prefectly for what I needed.
SELECT DISTINCT field1 , COUNT(*) as Counter FROM table1, GROUP BY field1 ORDER BY Counter DESC

ilan
2/17/07 3:21pm

Hi,
I have pasted some sample data. I am trying to get a unique count of the months per person. ID is unique and the primary key. More details below.

ID FIRSTNAME LASTNAME Month VISITS
2296015955 MAX SMART Feb-03 1
2296015955 MAX SMART Jan-03 2
2296015955 MAX SMART May-02 2
2296015955 MAX SMART Oct-02 2
2296016405 JOHN SMART Jun-02 2
2296016405 JOHN SMART May-03 2
2296086814 TEX JONES May-03 2
2296088711 FRANK FLOYE Jan-03 6
2296088711 FRANK FLOYE Mar-03 2
2296088711 FRANK FLOYE May-02 2

Here you can see Max Smart has had 1 visit in Feb 2003, 2 in Jan 2003 2 in May 2002 and
2 in October 2002. I got this by doing a Count function in Access.
What I need now is to find out the number of months Max Smart had visits in.
In this case it would be 4 ( Jan 03, Feb 03, May 02 and Oct 02)
For John Smart it would be 2 ( June 02 , May 03)
For Tex Jones it would be 1 ( May 03)
For Frank Floye it would be 3 ( Jan 03, March 03, May 02)

I cannnot do a Count for this because a person may have multiple visits in a month.
My goal is to find the number of months per person. How can I do this in Access.
Would appreciate any help.
Thanks,

Oggy
2/21/07 12:50pm

Try sum()

ken
4/5/07 8:48am

create a value of name and month-year. then you can do a count distinct on that value

Packman
5/15/07 11:50am

How to count records in a table which has column containing say
group create date modify date
A 1/12/02 2/12/03
A 2/12/02 03/02/03
A 03/02/03 05/03/03
B 05/03/03 06/04/03
C 06/04/03 07/05/03
A 07/05/03 08/05/03

I need Answer set
Group Count
A 3
B 1
C 1
A 1

Dave
5/25/07 10:45am

Jason, to do a count unique on two different fields you *could* concat the fields first ie:

SELECT DISTINCT concat(field1,’_',’field2) as field3 from table1

danny
8/24/07 7:05pm

Sounds good. If you want to count all records, regardless, just do SELECT count(*) FROM table_name;

Sean
12/4/07 8:44am

It doesn’t work for me either…

matey
12/30/07 10:44am

what about if we want to count the number of the repeated values in same column like 3,5? i want a statement lead to 3=2,5=2?

Joseph
4/7/08 1:52pm

let’s say i have a table with one column containing the following 6 records, and other column with the value 1,2,3,4,5,6,7,8,9:

3
3
4
5
5
6
is there a way with a single standard SQL query to get how many times a value is repeated?
The query should return
column1 columnn2
0 1
0 2
2 3
1 4
2 5
1 6
0 7
0 8
0 9

David
5/30/08 12:41pm

In SQL Server you’ll have to do something like:

SELECT COUNT(*) FROM (SELECT column id FROM tables ) AS alias