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.

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

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)

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

You need a separate commments space for your neatlinks.

matthew: hehe.

melanie: done. i moved your comment over there.

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?

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

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

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.

Jane try this:

SELECT Organisation, Branch
FROM TBL_Orgs
GROUP BY Organisation, Branch

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

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

Neat!
Thanks!

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.

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!

Man, I constantly refer back to this blog post.

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

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,

Try sum()

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

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

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

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

It doesn’t work for me either…

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?

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

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

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

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

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?

That did the trick. Thanks.

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

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 :)

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

Great help. Thank YOU!

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.

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

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

had the same question as Flag, thanks

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

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?

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!