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!

Feel free to if you found this useful.

62 Comments

Brian

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

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

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

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

Melanie

You need a separate commments space for your neatlinks.

justin

matthew: hehe.

melanie: done. i moved your comment over there.

Brian

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

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

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

Jane

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

Jane try this:

SELECT Organisation, Branch
FROM TBL_Orgs
GROUP BY Organisation, Branch

Abdul

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

justin

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

Neat!
Thanks!

Jason

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

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

Man, I constantly refer back to this blog post.

Michael

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

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

Try sum()

ken

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

Packman

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

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

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

Sean

It doesn’t work for me either…

matey

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

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

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

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

DaveS

To Joseph’s question
Select COUNT(DISTINCT column_name) as Expr1

Tera

But How do I count rows of data (in SQL) that is duplicated with different spelling, doesnt have a unique id. Such as:
id user
1 Justin
2 Justin’
3 Brian
4 Eve
4 Eve]
4 Jane
So i need to return the sum of user, which is 4 (Justin, Brian, Eve and Jane). How to count data with symbols & diff letter case in it?

Tod Birdsall

That did the trick. Thanks.

muthu

select count(*) from(select distinct user from table);

Stephen Groom

Thanks for this :) I was trying for ages to do ‘SELECT COUNT(*) FROM table GROUP BY column’ in MySQL and this is exactly what I needed :)

Jake

Good stuff – thanks for the help! This worked perfectly.

Luis

Great help. Thank YOU!

Flag

What about if I have this issue:

I have ony column and i whant to know how many times that row value for that column is repeating:

ex:
1
2
2
3
4
4
4

the result shouls look like this:

row val – numer of repetitions

1-1
2-2
3-1
4-3

Thanks in advance.

justin

flag, that’s actually pretty easy, just do this:

select column_name, count(*) 
from table_name group by column_name
Jay

had the same question as Flag, thanks

AJTESTER

I searched quite a few places for this and couldnt find the answer. Finally found it here thanks peeps!

Jef

How would I count multiple names in a single row of a column:
Ex.
Perf_org
Jim, Jason, Jeremy

Is it possible to count each name as one?

Juux

Spot on. Many sites I’ve been on said that using COUNT & DISTINCT on an MDB via ADO couldn’t be done, but this works perfectly and is even pretty fast.

Cheers!

saboya

SELECT column_name, COUNT(*)
FROM table_name
WHERE column_name = x
GROUP BY column_name

ilyas

thank you)

Naveed

Thank yoiu

Benjamin

You saved my life!!! Thanks for the distinct count guide!! You’re awesome!!!

Mon

What if I only want to display the record/s that only exists x times? (x is a variable).
Please help. ASAP.
Thanks.

justin

Mon, you need to use the HAVING clause. Try something like this:

select column_name 
from table_name 
group by column_name 
having count(column_name) = X

Remember to replace X with a number.

Mon

Thanks you so much justin!

Kyliegh

Hi,
can any one help me how to query count for one patient attending onn different dates to see how many time he has attended. I have thousand records and want to query how many times each patient has attended. For this i have two fields Patient No. and Arrival Date.

Any help will be apreciated.

srikanth

i want distinct values from single table where i want to display all the columns of distinct values

shamim yusuf

thank you

trev

i have the following and its returning the same result set for both months – is there any way to mod the query to include a like statement for both months instead of only the 1?

select distinct [sales contact],
COUNT([sent to customer date])
as ‘jan’,
count([sent to customer date])
as ‘feb’
from quotes
where [sent to customer date]
like ‘%feb-10%’ group by [Sales Contact]

EN

This was helpful. Thanks.

leoj

using the first example how about counting unique records by date?

Nator

Hi guys,dont know if this has been covered before in this site or not. I have a table full of raw that doesnt contain any constraints,nor primary or foreign but i needs to run a select statement that will exclude all the duplicate records which amount to 59,092

I used
SELECT YourColumn,Count(*)
FROM YourTable
GROUP BY YourColumn
HAVING COUNT(*) > 1

Nathan

Hi,
I have a db that has a state column. There are many different types of states. (50 I guess) I need to find out how many of each distinct states there are in the db.

So the output would be something like:
array:(ca,1)(wa,3)(va,6)

Nathan

Ha, Michael answered it from the past.
It was:
SELECT DISTINCT state, COUNT(*) as Counter
FROM table
GROUP BY state
ORDER BY Counter DESC;

Joey

Hi,
Your help for this querry will be verry appreciated !! I try to figure it out how I can count how many times a value like “a1” appear in a table for a user name. I have 124 possibles value and 10 columns for 12000 records in the table “errors”.

Name|col1|col2|col3| (10 colums)
Eric| a1 | c4 | a1 |
Joe | c2 | b1 | z4 |
Stef| s1 | q2 | l3 |

The results is suppose to look like:

Eric
a1 | 2
c4 | 1

Joe
c2 | 1
b1 | 1
z4 | 1

Thanks for your help !!

Regards,
Joey

webdeveloper

Check the following reference, various query related with count() function.
http://www.w3resource.com/sql/aggregate-functions/count-function.php

satya

This was helpful. Thanks.

Abab

thank you

Dibakar

hi can you please suggest why it is not working in mysql .
SELECT COUNT(*)
FROM (SELECT DISTINCT column_name FROM table_name)

How i need to modify for Mysql

Care to Comment?

Or if you'd prefer to get in touch privately, please send me an email.

Name

Email (optional)

Blog (optional)