Mysql - Best selling cars report using View

Question

An idea of how I can create a View performing the creation of a report in descending order with the most rented cars in a certain period of time. Displaying in this report, the vehicle plate, the total round mileage during the reported time period, the amount of leases held during the time period and the current mileage of the vehicle??

Structure of the above tables:

CREATE TABLE tbl_carros (
  ID_CARROS int(11) NOT NULL AUTO_INCREMENT,
  PLACA varchar(100) NOT NULL,
  MODELO varchar(100) NOT NULL,
  ANO varchar(100) NOT NULL,
  COR varchar(100) NOT NULL,
  QUILOM varchar(100) DEFAULT NULL,
  SITUACAO varchar(100) NOT NULL,
  DESCRICAO varchar(100) DEFAULT NULL,
  TBL_CLASSES_DO_CARRO_ID_CLASSES int(11) NOT NULL,
  PRIMARY KEY (ID_CARROS,TBL_CLASSES_DO_CARRO_ID_CLASSES),
  KEY fk_TBL_CARROS_TBL_CLASSES_DO_CARRO1_idx (TBL_CLASSES_DO_CARRO_ID_CLASSES),
  CONSTRAINT fk_TBL_CARROS_TBL_CLASSES_DO_CARRO1 FOREIGN KEY (TBL_CLASSES_DO_CARRO_ID_CLASSES) REFERENCES tbl_classes_do_carro (ID_CLASSES) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

CREATE TABLE tbl_reservas (
  MULTA int(11) DEFAULT NULL,
  VALOR_TOTAL int(11) NOT NULL,
  SITUACAO varchar(100) DEFAULT NULL,
  DATA_RETORNO date NOT NULL,
  DATA_LOCACAO date NOT NULL,
  QUILOM_RODADOS int(11) DEFAULT NULL,
  QUANT_DIARIA varchar(45) NOT NULL,
  TBL_CLIENTES_ID_CLIENTES int(11) NOT NULL,
  TBL_FILIAIS_ID_FILIAIS int(11) NOT NULL,
  TBL_CARROS_ID_CARROS int(11) NOT NULL, PRIMARY KEY (TBL_CLIENTES_ID_CLIENTES,TBL_FILIAIS_ID_FILIAIS,TBL_CARROS_ID_CARROS),
  KEY fk_TBL_RESERVAS_TBL_CLIENTES_idx (TBL_CLIENTES_ID_CLIENTES),
  KEY fk_TBL_RESERVAS_TBL_FILIAIS1_idx (TBL_FILIAIS_ID_FILIAIS),
  KEY fk_TBL_RESERVAS_TBL_CARROS1_idx (TBL_CARROS_ID_CARROS),
  CONSTRAINT fk_TBL_RESERVAS_TBL_CARROS1 FOREIGN KEY (TBL_CARROS_ID_CARROS) REFERENCES tbl_carros (ID_CARROS) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT fk_TBL_RESERVAS_TBL_CLIENTES FOREIGN KEY (TBL_CLIENTES_ID_CLIENTES) REFERENCES tbl_clientes (ID_CLIENTES) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT fk_TBL_RESERVAS_TBL_FILIAIS1 FOREIGN KEY (TBL_FILIAIS_ID_FILIAIS) REFERENCES tbl_filiais (ID_FILIAIS) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Answer 1

Follows below the select that you can use to generate your View.

Note: Remember to change the values of :menorData and :maiorData for the range you want.

SELECT 
    c.PLACA as Placa, SUM(r.QUILOM_RODADOS) as KMRodados,
    COUNT(1) as QTDLocacoesPeriodo, ANY_VALUE(c.QUILOM) as KMAtual
FROM
tbl_carros c
INNER JOIN tbl_reservas r ON r.TBL_CARROS_ID_CARROS = c.ID_CARROS
WHERE 
r.DATA_LOCACAO between ':menorData' and ':maiorData'
GROUP BY Placa
ORDER BY KMRodados DESC;