Count distinct values

Question

I have a data set asking a customer how many pets they have for example  Is there a way with one query I can count the distinct values  1 2 3  etc   Thanks    ---------- ------    Customer   Pets    ---------- ------          20      2           21      3           22      3           23      2           24      4    ---------- ------    What I want is a list saying    2 had 2 Pets 2 had 3 Pets 1 had 4 Pets

User · Answer

You can do a distinct count as follows   SELECT COUNT DISTINCT column name  FROM table name    EDIT   Following your clarification and update to the question  I see now that it s quite a different question than we d originally thought   DISTINCT  has special meaning in SQL  If I understand correctly  you want something like this    2 customers had 1 pets 3 customers had 2 pets 1 customers had 3 pets   Now you re probably going to want to use a subquery   select COUNT    column name FROM  SELECT DISTINCT column name     Let me know if this isn t quite what you re looking for

User · Answer

SELECT CUSTOMER  COUNT    as PETS  FROM table name  GROUP BY CUSTOMER

User · Answer

I think this link is pretty good   Sample output from that link   mysql gt  SELECT cate id COUNT DISTINCT pub lang    ROUND AVG no page  2      - gt  FROM book mast     - gt  GROUP BY cate id   --------- --------------------------- -----------------------    cate id   COUNT DISTINCT pub lang     ROUND AVG no page  2     --------- --------------------------- -----------------------    CA001                             2                  264 33      CA002                             1                  433 33      CA003                             2                  256 67      CA004                             3                  246 67      CA005                             3                  245 75     --------- --------------------------- -----------------------  5 rows in set  0 00 sec

User · Answer

You can use this   select count customer  as count  pets from table group by pets

User · Answer

Ok  I deleted my previous answer because finally it was not what willlangford was looking for  but I made my point that maybe we were all misunderstanding the question   I also thought of the SELECT DISTINCT    thing at first  but it seemed too weird to me that someone needed to know how many people had a different number of pets than the rest    thats why I thought that maybe the question was not clear enough   So  now that the real question meaning is clarified  making a subquery for this its quite an overhead  I would preferably use a GROUP BY clause   Imagine you have the table customer pets like this    -----------------------     customer      pets      ------------ ----------    customer1       2         customer2       3         customer3       2         customer4       2         customer5       3         customer6       4        ------------ ----------    then  SELECT count customer  AS num customers  pets FROM customer pets GROUP BY pets   would return    ----------------------------     num customers      pets      ----------------- ----------           3             2                2             3                1             4        ----------------- ----------    as you need

[mysql] Count distinct values

Examples related to mysql

Examples related to sql