select distinct and count distinct in mysql

Question

Is this possible Selecting distinct rows from a table column and count repeated row for each distinct fields in a single query

$sql = "SELECT DISTINCT location and COUNT(DISTINCT location) 
        FROM ".$db_name.".$table_name 
        ORDER BY location ASC 
        LIMIT ". $start_page.",". $recordPer_page."";

SELECT DISTINCT(location), COUNT(DISTINCT(location)) FROM .... — scalopus
– scalopus, Commented Oct 25, 2012 at 15:30

Justin · Accepted Answer · 2012-10-25 21:10:41Z

3

Hi this query help you:

SQLFIDDLE example

SELECT   `location`, COUNT(`location`) 
    FROM     tbl   
    GROUP BY `location`
    ORDER BY `location`

Data:

    ('Africa'),
    ('Viena'),
    ('Mexico'),
    ('Chicago'),
    ('Miami'),
    ('Chicago'),
    ('Viena'),
    ('London'),
    ('Viena');

Result:

| LOCATION | COUNT(`LOCATION`) |
--------------------------------
|   Africa |                 1 |
|  Chicago |                 2 |
|   London |                 1 |
|   Mexico |                 1 |
|    Miami |                 1 |
|    Viena |                 3 |

answered Oct 25, 2012 at 21:10

Justin

9,7336 gold badges38 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John Woo · Accepted Answer · 2012-10-25 15:29:59Z

0

Try,

SELECT   `location`, COUNT(DISTINCT `location`) 
FROM     ....   
GROUP BY `Location`
ORDER BY ....
LIMIT    ....

answered Oct 25, 2012 at 15:29

John Woo

265k70 gold badges509 silver badges500 bronze badges

1 Comment

lance.dolan Over a year ago

returns 1 in second column for all rows.

Collectives™ on Stack Overflow

select distinct and count distinct in mysql

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related