www.webdeveloper.com
Results 1 to 2 of 2

Thread: Count Distinct Values

  1. #1
    Join Date
    Jul 2010
    Location
    /ramdisk/
    Posts
    865

    Count Distinct Values

    I want to select distinct values, and the count of the distinct values.

    An example table would be:

    Code:
    field
    +-------+
    |   A   |
    |   A   |
    |   B   |
    +-------+
    I want my query to return:
    Code:
    | field | count(field) |
    +-------+--------------+
    |   A   |      2       |
    |   B   |      1       |
    +-------+--------------+
    I've tried permutations of count(distinct(field), field etc etc but I just end up with one row- when from looking at SELECT *, I can clearly see the query I'm wanting returned should have at least 100 rows.

    Edit: I know I can do this with php or two queries/stage table, but this really should be a 1 query thing.

  2. #2
    Join Date
    Jul 2010
    Location
    /ramdisk/
    Posts
    865
    Turns out I got it. I tried a sub-query but the number of rows didn't match up, so I fell back on my googlefu and found an answer.

    Code:
    SELECT DISTINCT table.field, Count(table.field) as Number 
    FROM table 
    GROUP BY table.field

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles