Why in order to count the number of duplicate values ​​in a field, you can write:

SELECT *, COUNT(phone) FROM registered_phones GROUP BY phone HAVING COUNT(1)>1;

And in order to calculate the number of non-repeating values, you need to write:

 SELECT COUNT(*) FROM ( SELECT DISTINCT phone FROM registered_phones ) t;

and you can not write:

 SELECT DISTINCT phone, COUNT(phone) FROM registered_phones;

This last query will return an invalid value. What is the reason for this?

UPD:

I also noticed that after all, you can return the correct number of non-repeating values ​​by the following query:

 SELECT DISTINCT phone, COUNT(DISTINCT phone) FROM registered_phones;

    2 answers 2

    SELECT COUNT(*) FROM ( SELECT DISTINCT phone FROM registered_phones ) t;

    Select phone , discard duplicate and count the number of remaining

    SELECT DISTINCT phone, count(phone) FROM registered_phones;

    For all records, display the values ​​of the phone field and the number of records in the table, where the phone NOT NULL. From pairs ( phone , count ) discard duplicates

      1. Because we group by line and see how many times it occurs

         SELECT *, COUNT(phone) FROM registered_phones GROUP BY phone HAVING COUNT(1) > 1

      2. What hinders to search for non-repeating values ​​to write like this?

         SELECT *, COUNT(phone) FROM registered_phones GROUP BY phone HAVING COUNT(1) = 1

      3. Because this query will first calculate the number - make a table from phone and count(phone) , and then go through again and execute DISTINCT , that is, delete all duplicates (which will be useless, because we use aggregating functions and grouping):

         SELECT DISTINCT phone, COUNT(phone) FROM registered_phones GROUP BY phone;

        In this case, you should use the duplicate deletion when counting the quantity, that is, use COUNT(DISTINCT phone)

      • "Because this query will first do DISTINCT, that is, delete all duplicates, and then only count the number" - so that’s what seems to be the return of the correct number of non-duplicate values ​​(that is, the number of records without duplicates). But the result shows that it is not. Why is this coming out? - Ksenia
      • @Ksenia is in the wrong order inadvertently written, corrected. - Denis