SEO

#1 (**permalink**) 02-28-2008, 09:26 AM

How do I count the unique values in a column in a very large table?

I could do this:

select mycolumn
from mytable
group by mycolumn;

and wait to see how many rows come back. The trouble is that the table
has 75 million rows, and the MySQL Query Browser crashes with an
out-of-memory error after 5 million rows have come back.

All is need is a single number: the count. There's got to be a way to do
it in SQL alone.

#2 (**permalink**) 02-28-2008, 09:26 AM

On 19.03.2007 18:11, Chris wrote:
> How do I count the unique values in a column in a very large table?
>
> I could do this:
>
> select mycolumn
> from mytable
> group by mycolumn;
>
> and wait to see how many rows come back. The trouble is that the table
> has 75 million rows, and the MySQL Query Browser crashes with an
> out-of-memory error after 5 million rows have come back.
>
> All is need is a single number: the count. There's got to be a way to do
> it in SQL alone.

count(distinct mycolumn)

robert

#3 (**permalink**) 02-28-2008, 09:26 AM

On Mar 19, 5:11 pm, Chris <spam_me_...@goaway.com> wrote:
> How do I count the unique values in a column in a very large table?
>
> I could do this:
>
> select mycolumn
> from mytable
> group by mycolumn;
>
> and wait to see how many rows come back. The trouble is that the table
> has 75 million rows, and the MySQL Query Browser crashes with an
> out-of-memory error after 5 million rows have come back.
>
> All is need is a single number: the count. There's got to be a way to do
> it in SQL alone.

I think 'values that only appear once' can be found like this:

SELECT t1.mycolumn, t2.mycolumn
FROM mytable t1
LEFT JOIN mytable t2 ON t2.word_id <> t1.word_id
AND t2.mycolumn = t1.mycolumn
WHERE isnull( t2.mycolumn )

#4 (**permalink**) 02-28-2008, 09:26 AM

Robert Klemme wrote:
> On 19.03.2007 18:11, Chris wrote:
>> How do I count the unique values in a column in a very large table?
>>
>> I could do this:
>>
>> select mycolumn
>> from mytable
>> group by mycolumn;
>>
>> and wait to see how many rows come back. The trouble is that the table
>> has 75 million rows, and the MySQL Query Browser crashes with an
>> out-of-memory error after 5 million rows have come back.
>>
>> All is need is a single number: the count. There's got to be a way to
>> do it in SQL alone.
>
> count(distinct mycolumn)
>
> robert

This indicates the right syntax:

http://dev.mysql.com/doc/refman/5.0/...functions.html

But I tried:

select count (distinct session_id) from event_test;

and got:

You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near 'distinct
session_id) from event_test at line 1.

The table and the column name is correct. What am I doing wrong?

#5 (**permalink**) 02-28-2008, 09:26 AM

On Mar 19, 6:39 pm, Chris <spam_me_...@goaway.com> wrote:
> Robert Klemme wrote:
> > On 19.03.2007 18:11, Chris wrote:
> >> How do I count the unique values in a column in a very large table?
>
> >> I could do this:
>
> >> select mycolumn
> >> from mytable
> >> group by mycolumn;
>
> >> and wait to see how many rows come back. The trouble is that the table
> >> has 75 million rows, and the MySQL Query Browser crashes with an
> >> out-of-memory error after 5 million rows have come back.
>
> >> All is need is a single number: the count. There's got to be a way to
> >> do it in SQL alone.
>
> > count(distinct mycolumn)
>
> > robert
>
> This indicates the right syntax:
>
> http://dev.mysql.com/doc/refman/5.0/...functions.html
>
> But I tried:
>
> select count (distinct session_id) from event_test;
>
> and got:
>
> You have an error in your SQL syntax; check the manual that corresponds
> to your MySQL server version for the right syntax to use near 'distinct
> session_id) from event_test at line 1.
>
> The table and the column name is correct. What am I doing wrong?

When it says 'near', it nearly always means 'immediately to the left
of'!

Try removing the space between count and (

#6 (**permalink**) 02-28-2008, 09:26 AM

>> select count (distinct session_id) from event_test;
>>
>> and got:
>>
>> You have an error in your SQL syntax; check the manual that corresponds
>> to your MySQL server version for the right syntax to use near 'distinct
>> session_id) from event_test at line 1.
>>
>> The table and the column name is correct. What am I doing wrong?
>
> When it says 'near', it nearly always means 'immediately to the left
> of'!
>
> Try removing the space between count and (
>

Thanks. I'm a complete dope.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

How to count unique values in a column?