Professional Web Applications Themes

How to count unique values in a column? - MySQL

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....

  1. #1

    Default How to count unique values in a column?

    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.
    Chris Guest

  2. #2

    Default Re: How to count unique values in a column?

    On 19.03.2007 18:11, Chris wrote: 

    count(distinct mycolumn)

    robert
    Robert Guest

  3. #3

    Default Re: How to count unique values in a column?

    On Mar 19, 5:11 pm, Chris <com> wrote: 

    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 )

    strawberry Guest

  4. #4

    Default Re: How to count unique values in a column?

    Robert Klemme wrote: 
    >
    > count(distinct mycolumn)
    >
    > robert[/ref]

    This indicates the right syntax:

    http://dev.mysql.com/doc/refman/5.0/en/group-by-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?


    Chris Guest

  5. #5

    Default Re: How to count unique values in a column?

    On Mar 19, 6:39 pm, Chris <com> wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]


    >
    > This indicates the right syntax:
    >
    > http://dev.mysql.com/doc/refman/5.0/en/group-by-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?[/ref]

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

    Try removing the space between count and (

    strawberry Guest

  6. #6

    Default Re: How to count unique values in a column?

    >> select count (distinct session_id) from event_test; 
    >
    > When it says 'near', it nearly always means 'immediately to the left
    > of'!
    >
    > Try removing the space between count and (
    >[/ref]

    Thanks. I'm a complete dope.
    Chris Guest

Similar Threads

  1. Count unique data in column
    By kev in forum MySQL
    Replies: 10
    Last Post: June 2nd, 08:51 PM
  2. Select after insert to the unique column
    By Bruno Wolff III in forum PostgreSQL / PGSQL
    Replies: 3
    Last Post: December 13th, 04:44 PM
  3. Replies: 0
    Last Post: July 7th, 08:32 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139