Help me write the best query in mysql

Question

PHP

In the course of finalizing the telephone rater, I encountered the following problem.
We have logs stored in MYSQL from the telephone exchange, which are calculated by a separate script and store the cost of all calls made for each phone. They can be viewed through a special page written in php, which brings all the data for the month into one table.
Now we need to add the ability to bind each phone number ( in_number ) to a specific department, and over time, numbers can move from one department to another ( not necessarily at the beginning of the month ). Accordingly, the statistics in the new version should be displayed in the table by departments in this order:

1. Department name
2. Amount spent per room this month
3. Amount of costs per department

As a matter of fact, interests as most optimum to write request on data sampling for this table ? Is it possible to calculate this in real time or will it be necessary to prepare data for it in advance, for example. every day at night?

You can see a simplified version of the base below:

/* Таблица с внутренними номерами */
CREATE TABLE IF NOT EXISTS `in_numbers` (
 `in_number` INT(3) UNSIGNED NOT NULL AUTO_INCREMENT ,
 PRIMARY KEY (`in_number`) ,
 UNIQUE INDEX `in_number_UNIQUE` (`in_number` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
/* Таблица со статистикой звонков */
CREATE TABLE IF NOT EXISTS `outgoing` (
 `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
 `d_time` TIMESTAMP NULL DEFAULT NULL ,
 `in_number` INT(3) UNSIGNED NOT NULL ,
 `t_call` TIME NULL DEFAULT NULL ,
 `c_number` BIGINT(20) UNSIGNED NULL DEFAULT NULL ,
 `code` BIGINT(20) UNSIGNED NULL DEFAULT NULL ,
 `c_price` FLOAT(11,3) NULL DEFAULT NULL
 PRIMARY KEY (`id`) ,
 CONSTRAINT `outgoing_in_number`
  FOREIGN KEY (`in_number` )
  REFERENCES `in_numbers` (`in_number` )
  ON DELETE CASCADE
  ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
/* Таблица существующих департаментов */
CREATE TABLE IF NOT EXISTS `departments` (
 `department_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
 `department` VARCHAR(255) NOT NULL ,
 PRIMARY KEY (`department_id`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
/* Таблица истории привязок номеров к департаментам */
CREATE TABLE IF NOT EXISTS `assigments` (
 `assigment_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
 `in_number` INT(3) UNSIGNED NOT NULL ,
 `department_id` INT(10) UNSIGNED NOT NULL ,
 `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
 PRIMARY KEY (`assigment_id`) ,
 INDEX `assigment_dep_id` (`department_id` ASC) ,
 INDEX `assigment_number` (`in_number` ASC) ,
 CONSTRAINT `assigment_dep_id`
  FOREIGN KEY (`department_id` )
  REFERENCES `departments` (`department_id` )
  ON DELETE CASCADE
  ON UPDATE CASCADE,
 CONSTRAINT `assigment_number`
  FOREIGN KEY (`in_number` )
  REFERENCES `in_numbers` (`in_number` )
  ON DELETE CASCADE
  ON UPDATE CASCADE)
ENGINE = InnoDB;

* This source code was highlighted with Source Code Highlighter.

Answer 1

I think base denormalization will help you. Just add the department_id field to the table with call logs, and then you can group by it, and inside it group by numbers