MySQL query DISTINCT, COUNT

Question

I'm trying to use a SELECT DISTINCT to find out how many people have cars, how many have boats, now many bikes and so on, from this table

|   name   |   obj  |
---------------------
|  john    |  car   |
|  mary    |  boat  |
|  dave    |  car   |
|  james   |  bike  |
|  steve   |  car   |
|  walt    |  bike  |

ex: bike - 2

select distinct works on a row-basis. it's not filtering for distinct field contents. you need a select obj, count(obj) ... group by obj instead. — Marc B
– Marc B, Commented Sep 5, 2014 at 21:56

Justin Iurman · Accepted Answer · 2014-09-05 22:08:24Z

1

You can use this query

SELECT obj, COUNT(name) as nb FROM table GROUP BY obj ORDER BY obj

Result (sqlfiddle):

|   obj    |   nb   |
---------------------
|  bike    |    2   |
|  boat    |    1   |
|  car     |    3   |

edited Sep 5, 2014 at 22:08

answered Sep 5, 2014 at 22:03

Justin Iurman

19k3 gold badges39 silver badges58 bronze badges

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

Comments

juergen d · Accepted Answer · 2014-09-05 21:57:06Z

0

select sum(obj = 'car') as car_count,
       sum(obj = 'boat') as boat_count
from your_table

answered Sep 5, 2014 at 21:57

juergen d

205k40 gold badges303 silver badges377 bronze badges

1 Comment

Blueleaf Over a year ago

Could work, but what if i have 100 types of objects? In my case I have 23 different objects. Thanks for the answer!

Collectives™ on Stack Overflow

MySQL query DISTINCT, COUNT

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