Why I get error group by result of subquery?

I want to find the number of trace records which has same telephone,project_id,create_time,trace_content

But this sql:

select count(1) from trace WHERE  DATE(create_time)="2020-01-16" AND 
  id not in (
     select id from trace WHERE DATE(create_time)="2020-01-16"  
     GROUP BY telephone,project_id,create_time,trace_content
);

returns wrong result. If I use:

select count(1) from trace WHERE  DATE(create_time)="2020-01-16" AND 
    id not in (
               select id FROM (
                      select id from trace WHERE DATE(create_time)="2020-01-16"  
                      GROUP BY telephone,project_id,create_time,trace_content
              ) a
);

returns correct result. Mysql version is 5.6.26 Could anyone tell me the reason？

For example the table contains these data:

1. id = 1, project_id = 1, telephone = 1, trace_contest = test, creatime = 2020-01-16 18:27:46

2. id = 2, project_id = 1, telephone = 1, trace_contest = test, creatime = 2020-01-16 18:27:46

3. id = 3, project_id = 2, telephone = 2, trace_contest = test1, creatime = 2020-01-16 18:22:27

fisrt sql return 0, second sql return 1

Answer

Both of your queries are actually wrong: your are selecting id but it is not not part of the group by clause, so you basically get a random value out of the group. In most RDBMS (and in non-ancient versions of MySQL), this would generate a syntax error.

I understand that you want to count how many (telephone, project_id, create_time, trace_content) occur more than once in the table.

You can use two levels of aggregation for this:

select count(*) cnt_duplicates
from (
    select 1
    from mytable
    where create_time >= '2020-01-16' and create_time < '2020-01-17'
    group by telephone, project_id, create_time, trace_content
    having count(*) > 1
) t

The inner query groups records and filters on those that occur more than one. The outer query counts how many records were returned by the inner query.

Note that I rewrote your condition on the date so it can take advantage of an index on create_time, if any.