There is a table with sources of calls:

id | name ------------------ 1 | Источник 1 2 | Источник 2 3 | Источник 3

And a table with the number of these calls:

  id | date | operator | id_source | sum --------------------------------------------- 1 |2016-08-31| ivan | 1 | 32 2 |2016-08-31| ivan | 2 | 25 3 |2016-08-31| ivan | 3 | 10 4 |2016-08-31| petr | 1 | 8 5 |2016-08-31| petr | 2 | 7 6 |2016-08-31| petr | 3 | 19 7 |2016-08-30| petr | 1 | 5 8 |2016-08-30| petr | 2 | 26 9 |2016-08-30| petr | 3 | 45

As a result, you need to make sure that the profile, for example, Peter, has a summary table for his received calls, like:

  Дата | Источник 1 | Источник 2 | Источник 3 ------------------------------------------------- 2016-08-31 | 8 | 7 | 19 2016-08-30 | 5 | 26 | 45

The number of sources may vary, therefore a separate table has been created. Previously, the number of sources was the same, therefore, it managed with one table.

Help me decide, the second day I puzzle how to implement it. I would be grateful for any tip or example.

  • There is a similar task here, Link: stratosprovatopoulos.com/web-development/mysql/… - ivan.m
  • I think something like SELECT date, источник, sum(sum) FROM звонки JOIN источники ON источники.id=звонки.id_source GROUP BY date this will return SELECT date, источник, sum(sum) FROM звонки JOIN источники ON источники.id=звонки.id_source GROUP BY date compressed data, but vertically. And in the horizontal line it is necessary to deploy already in PHP. For example, using grouping in PDO: ru.stackoverflow.com/questions/504422/php/504445#504445 - Mike

1 answer 1

One of the possible solutions to the problem is to group by date using the GROUP_CONCAT() function

 SELECT `date`, GROUP_CONCAT(sum ORDER BY id_source) AS calls FROM calls WHERE operator = 'petr' GROUP BY `date` ORDER BY `date` DESC

The query returns two columns, in the first the date, in the second - the comma-separated values ​​of the sum sorted by source_id . In order to form a table source_id enough to request data from the table of call sources just by sorting them by source_id .

  • Controversial, because it does not get the names of the sources and if some of the dates do not say, say source2, then there will be a line only in sum 1 and 3 sources and it is impossible to understand which columns to put them on the client. - Mike
  • @Mike perhaps yes - cheops
  • Thank you for your attention, I found the answer, the method is called PIVOT - khripunovpp