We have a table manager - incoming number:

call_time | manager | aon 10:01 | user1 | 7972207 10:14 | user2 | 8581041 10:20 | user3 | 3642345 11:00 | user1 | 8089142 11:01 | user2 | 7972207 11:13 | user2 | 0297188 11:22 | user1 | 8089142 11:34 | user3 | 3642345 11:50 | user4 | 3642345

You need to get the number of unique calls to managers. But not every manager has unique calls, but unique calls in general. The unique call in this case is each unique aon . The table has a timestamp, so whoever first received the call, we count that. For example, 3642345 has already accepted user3 , therefore it is not necessary to consider it for user4 .

 user1 2 user2 2 user3 1 user4 0
  • You need to select unique numbers and group them by manager. - MaximK
  • one
    According to what principle does the number correspond to the manager in the result set? Why is the number 3642345 counted for the user3 manager, and not for the user4 manager? - kodv
  • What does "unique call" mean? Is it aon that other aon do not have, or is it aon that doesn’t repeat with one manager ? - i-one
  • @kodv because 3642345 has already accepted user3, so no need to consider it for user4 - Pavel Sokolov
  • one
    @PavelSokolov How to determine that this call was received for the first time by the user3 manager, and not by the user4 manager? Is there any field in the table with a call reception time? The DBMS does not guarantee the withdrawal of strict in accordance with the chronology of their creation. - kodv

3 answers 3

Number of unique calls sorted by the time of first number processing:

 SELECT `manager`, COUNT(*) AS `calls` FROM ( SELECT * FROM ( SELECT `manager`, `aon` FROM `calls` ORDER BY `call_time` ASC ) t1 GROUP BY `aon` ) t2 GROUP BY `manager`
  • It seems to fit. Only without grouping on manager in nested. - Pavel Sokolov
  • The one who first received. - Pavel Sokolov
  • Then you must first sort the calls by the time they arrive. updated the answer - Darevill
  • @PavelSokolov If the answer given for MySQL completely triples you, please put the MySQL tag in the question first, and not just SQL. For in general terms, for an arbitrary DBMS, this answer will not be working, because in most DBMSs in the GROUP BY there must be all the columns that are in the selection list and to which the aggregate functions are not applied. Although in the case of MySQL the solution is really beautiful - Mike
  SELECT distinct(calls) FROM calls;

    SELECT DISTINCT [query] - removes all repetitions.

    I do not know what your table is called, but SELECT DISTINCT aon from [название_таблицы] will return all numbers to you, without repetition.