Return a query taking data from three tables

0

I need the result of three tables in a query to generate a google charts chart.

table 1: // This will be column One

SELECT SUBSTRING(dt_vencimento,1,7) as anomes, SUM(vl_valor) as valor_total 
FROM escola.tb_despesas_administrativas
where YEAR(dt_vencimento) =2018
GROUP BY anomes;

I have to add the rest of table 2 and 3

table 2: // This will be column 2

SELECT SUBSTRING(dt_vencimento,1,7) as anomes, SUM(vl_boleto) as valor_total 
FROM escola.tb_pedido
where YEAR(dt_vencimento) =2018
GROUP BY anomes;

table 3: // This will also be column 2

SELECT SUBSTRING(dt_vencimento,1,7) as anomes, SUM(vl_parcela) as valor_total 
FROM escola.tb_carne_matricula
where YEAR(dt_vencimento) =2018
GROUP BY anomes;

The result would be something like this.

   anomes  |valor_totalDebito(tb_1) |valor_totalCredito (tb_2) |
    --------|------------------------|--------------------------|
    2018-02 |142.30                  |123.23                    |
    2018-03 |776.05                  |423.11                    |
    2018-04 |251.05                  |443.21                    |
    2018-05 |251.05                  |112.33                    |
    2018-06 |251.05                  |242.22                    |
    2018-07 |232.30                  |121.34                    |
    2018-08 |42.30                   |332.22                    |
    2018-09 |42.30                   |111.32                    |
    2018-10 |42.30                   |543.33                    |
    2018-11 |42.30                   |443.22                    |
    2018-12 |42.30                   |342.56                    |
    --------|------------------------|--------------------------|

I'll send the bank, in case anyone wanted to help, I thank you.

CREATE TABLE 'tb_carne_matricula' (
  'cd_carne_matricula' int(11) NOT NULL AUTO_INCREMENT,
  'nu_parcela' int(11) DEFAULT NULL,
  'nu_parcelatotal' int(11) DEFAULT NULL,
  'vl_parcela' varchar(45) DEFAULT NULL,
  'cd_matricula' int(11) NOT NULL,
  'bo_situacao_pagamento' tinyint(4) DEFAULT NULL,
  'dt_vencimento' date DEFAULT NULL,
  PRIMARY KEY ('cd_carne_matricula'),
  KEY 'fk_tb_carne_matricula_tb_matricula1_idx' ('cd_matricula'),
  CONSTRAINT 'fk_tb_carne_matricula_tb_matricula1' FOREIGN KEY ('cd_matricula') REFERENCES 'tb_matricula' ('cd_matricula') ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=136 DEFAULT CHARSET=latin1;

INSERT INTO 'tb_carne_matricula' VALUES (86,1,3,'108.33333333333',64,0,'2018-03-11'),(87,2,3,'108.33333333333',64,1,'2018-03-11'),(88,3,3,'108.33333333333',64,1,'2018-03-11'),(101,1,13,'42.307692307692',66,0,'2018-02-09'),(102,2,13,'42.307692307692',66,0,'2018-03-09'),(103,3,13,'42.307692307692',66,0,'2018-04-09'),(104,4,13,'42.307692307692',66,0,'2018-05-09'),(105,5,13,'42.307692307692',66,0,'2018-06-09'),(106,6,13,'42.307692307692',66,0,'2018-07-09'),(107,7,13,'42.307692307692',66,0,'2018-08-09'),(108,8,13,'42.307692307692',66,0,'2018-09-09'),(109,9,13,'42.307692307692',66,0,'2018-10-09'),(110,10,13,'42.307692307692',66,0,'2018-11-09'),(111,11,13,'42.307692307692',66,0,'2018-12-09'),(112,12,13,'42.307692307692',66,0,'2019-01-09'),(113,13,13,'42.307692307692',66,0,'2019-02-09'),(114,1,2,'100',67,1,'2018-02-15'),(115,2,2,'100',67,0,'2018-03-15'),(116,1,5,'50',68,0,'2018-03-11'),(117,2,5,'50',68,0,'2018-04-11'),(118,3,5,'50',68,0,'2018-05-11'),(119,4,5,'50',68,0,'2018-06-11'),(120,5,5,'50',68,0,'2018-07-11'),(121,1,5,'100',69,1,'2018-03-15'),(122,2,5,'100',69,0,'2018-04-15'),(123,3,5,'100',69,0,'2018-05-15'),(124,4,5,'100',69,0,'2018-06-15'),(125,5,5,'100',69,0,'2018-07-15'),(126,1,5,'40',70,1,'2018-03-15'),(127,2,5,'40',70,0,'2018-04-15'),(128,3,5,'40',70,0,'2018-05-15'),(129,4,5,'40',70,0,'2018-06-15'),(130,5,5,'40',70,0,'2018-07-15'),(131,1,1,'100',71,0,'2018-03-11'),(132,1,4,'18.75',72,0,'2018-03-25'),(133,2,4,'18.75',72,0,'2018-04-25'),(134,3,4,'18.75',72,0,'2018-05-25'),(135,4,4,'18.75',72,0,'2018-06-25');

DROP TABLE IF EXISTS 'tb_despesas_administrativas';
CREATE TABLE 'tb_despesas_administrativas' (
  'cd_despesas_administrativas' int(11) NOT NULL AUTO_INCREMENT,
  'dt_competencia' date DEFAULT NULL,
  'dt_vencimento' date DEFAULT NULL,
  'vl_valor' decimal(10,2) DEFAULT NULL,
  'nu_documento' text,
  'nu_repetir' int(11) DEFAULT NULL,
  'ds_despesas' text,
  'bo_situacao_pagamento' tinyint(4) DEFAULT NULL,
  'dt_pagamento' date DEFAULT NULL,
  'vl_juros' decimal(10,2) DEFAULT NULL,
  'vl_multa' decimal(10,2) DEFAULT NULL,
  'vl_pago' decimal(10,2) DEFAULT NULL,
  'repetir_numero' int(11) DEFAULT NULL,
  'cd_categoria_despesas' int(11) NOT NULL,
  PRIMARY KEY ('cd_despesas_administrativas'),
  KEY 'fk_tb_despesas_administrativas_tb_categoria_despesas1_idx' ('cd_categoria_despesas'),
  CONSTRAINT 'fk_tb_despesas_administrativas_tb_categoria_despesas1' FOREIGN KEY ('cd_categoria_despesas') REFERENCES 'tb_categoria_despesas' ('cd_categoria_despesas') ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=116 DEFAULT CHARSET=latin1;

INSERT INTO 'tb_despesas_administrativas' VALUES (85,'2018-01-01','2018-01-01',520.00,'123',12,'Teste',1,'2018-01-01',0.00,0.00,520.00,1,1),(86,'2018-01-01','2018-01-01',520.00,'123',12,'Teste Alterar',0,NULL,0.00,0.00,521.00,NULL,1),(87,'2018-01-01','2018-01-01',520.00,'123',12,'Teste',0,'2018-01-01',0.00,0.00,520.00,1,1),(88,'2018-01-01','2018-01-01',520.00,'123',12,'Teste',1,'2018-01-01',0.00,0.00,520.00,4,1),(89,'2018-01-01','2018-01-01',520.00,'123',12,'Teste',1,'2018-01-01',0.00,0.00,520.00,5,1),(90,'2018-01-01','2018-01-01',520.00,'123',12,'Teste',1,'2018-01-01',0.00,0.00,520.00,6,1),(91,'2018-01-01','2018-01-01',520.00,'123',12,'Teste',1,'2018-01-01',0.00,0.00,520.00,7,1),(92,'2018-01-01','2018-01-01',520.00,'123',12,'Teste',1,'2018-01-01',0.00,0.00,520.00,8,1),(93,'2018-01-01','2018-01-01',520.00,'123',12,'Teste',1,'2018-01-01',0.00,0.00,520.00,9,1),(94,'2018-01-01','2018-01-01',520.00,'123',12,'Teste',1,'2018-01-01',0.00,0.00,520.00,10,1),(95,'2018-01-01','2018-01-01',520.00,'123',12,'Teste',1,'2018-01-01',0.00,0.00,520.00,11,1),(96,'2018-01-01','2018-01-01',520.00,'123',12,'Teste',1,'2018-01-01',0.00,0.00,520.00,12,1),(97,'2018-01-01','2018-01-01',520.00,'123',12,'Teste',1,NULL,0.00,0.00,520.00,12,1),(98,'2018-02-01','2018-03-22',350.00,'133',1,'Pagamento da mensalidade do sistema',0,'2018-02-01',0.00,0.00,350.00,1,3),(100,'2018-01-08','2018-02-01',200.00,'0',3,'CELG DISTRIBUIÇÃO',1,'2018-02-10',0.00,0.00,200.00,1,2),(101,'2018-01-08','2018-02-01',200.00,'0',3,'energia',0,'2018-02-10',0.00,0.00,0.00,3,2),(102,'2018-03-17','2018-04-11',250.00,'23',1,'Teste',1,'2018-03-11',0.00,0.00,250.00,1,1),(103,'2018-04-17','2018-04-17',275.50,'12',1,'Despesas teste competencia',0,'2018-03-17',0.00,0.00,0.00,1,2),(104,'2018-03-20','2018-03-25',250.00,'123',9,'Pagar Sharles',0,'2018-03-20',0.00,0.00,0.00,1,1),(105,'2018-03-20','2018-03-25',250.00,'123',9,'Pagar Sharles',0,'2018-03-20',0.00,0.00,0.00,2,1),(106,'2018-03-20','2018-03-25',250.00,'123',9,'Pagar Sharles',1,'2018-03-20',0.00,0.00,250.00,1,1),(107,'2018-03-20','2018-03-25',250.00,'123',9,'Pagar Sharles',0,'2018-03-20',0.00,0.00,0.00,4,1),(108,'2018-03-20','2018-03-25',250.00,'123',9,'Pagar Sharles',0,'2018-03-20',0.00,0.00,0.00,5,1),(109,'2018-03-20','2018-03-25',250.00,'123',9,'Pagar Sharles',0,'2018-03-20',0.00,0.00,0.00,6,1),(110,'2018-03-20','2018-03-25',250.00,'123',9,'Pagar Sharles',0,'2018-03-20',0.00,0.00,0.00,7,1),(111,'2018-03-20','2018-03-25',250.00,'123',9,'Pagar Sharles',0,'2018-03-20',0.00,0.00,0.00,8,1),(112,'2018-03-20','2018-03-25',250.00,'123',9,'Pagar Sharles',0,'2018-03-20',0.00,0.00,0.00,9,1),(113,'2018-04-01','2018-04-12',300.00,'SN',1,'Energia',0,'2018-04-01',0.00,0.00,0.00,1,2),(114,'2018-04-01','2018-04-15',100.00,'12',1,'Despesa teste',0,'2018-04-01',0.00,0.00,0.00,1,4),(115,'2018-04-01','2018-04-12',1000.00,'1',1,'Outra',0,'2018-04-01',0.00,0.00,0.00,1,4);

CREATE TABLE 'tb_pedido' (
  'cd_pedido' int(11) NOT NULL AUTO_INCREMENT,
  'vl_boleto' decimal(10,2) DEFAULT NULL,
  'dt_referente' date DEFAULT NULL,
  'vl_desconto' decimal(10,2) DEFAULT NULL,
  'dt_gerado' date DEFAULT NULL,
  'situacao' tinyint(4) DEFAULT NULL,
  'nu_documento' text,
  'bo_envio_remessa' tinyint(4) DEFAULT NULL,
  'bo_pedido_automatico' tinyint(4) DEFAULT NULL,
  'bo_impresso' tinyint(4) DEFAULT NULL,
  'dt_vencimento' date DEFAULT NULL,
  'dt_pagamento' date DEFAULT NULL,
  'bo_multa' tinyint(4) DEFAULT NULL,
  'bo_matricula' tinyint(4) DEFAULT NULL,
  'bo_cobrar_multa' tinyint(4) DEFAULT NULL,
  'bo_cobrar_juros' tinyint(4) DEFAULT NULL,
  'bo_dar_desconto' tinyint(4) DEFAULT NULL,
  'vl_boleto_reajustado' decimal(10,2) DEFAULT NULL,
  'vl_boleto_pago_no_banco' decimal(10,2) DEFAULT NULL,
  'vl_taxa_cobrado_pelo_banco' decimal(10,2) DEFAULT NULL,
  'cd_usuario' int(11) NOT NULL,
  'cd_aluno' int(11) NOT NULL,
  PRIMARY KEY ('cd_pedido'),
  KEY 'fk_tb_pedido_tb_usuario1_idx' ('cd_usuario'),
  KEY 'fk_reference_5' ('cd_aluno'),
  CONSTRAINT 'fk_reference_5' FOREIGN KEY ('cd_aluno') REFERENCES 'tb_aluno' ('cd_aluno'),
  CONSTRAINT 'fk_tb_pedido_tb_usuario1' FOREIGN KEY ('cd_usuario') REFERENCES 'tb_usuario' ('cd_usuario') ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=48 DEFAULT CHARSET=latin1;

INSERT INTO 'tb_pedido' VALUES (39,153.55,'2018-03-15',0.00,'2018-03-20',1,'',0,0,0,'2018-03-20','2018-03-20',1,0,1,1,1,153.55,153.55,0.00,1,19),(40,138.25,'2018-03-15',0.00,'2018-03-20',0,'',0,0,0,'2018-03-20','2018-03-20',1,0,1,1,1,138.25,0.00,0.00,1,59),(41,114.75,'2018-03-15',0.00,'2018-03-20',0,'',0,0,0,'2018-03-09','2018-03-20',1,0,1,1,1,114.75,0.00,0.00,1,77),(42,153.00,'2018-03-15',0.00,'2018-03-20',0,'',0,0,0,'2018-03-09','2018-03-20',1,0,1,1,1,153.00,0.00,0.00,1,79),(43,137.70,'2018-03-15',0.00,'2018-03-20',0,'',0,0,0,'2018-03-09','2018-03-20',1,0,1,1,1,137.70,0.00,0.00,1,80),(44,76.50,'2018-03-15',0.00,'2018-03-20',0,'',0,0,0,'2018-03-09','2018-03-20',1,0,1,1,1,76.50,0.00,0.00,1,81),(45,153.00,'2018-03-15',0.00,'2018-03-20',0,'',0,0,0,'2018-03-09','2018-03-20',1,0,1,1,1,153.00,0.00,0.00,1,82),(46,38.36,'2018-03-15',0.00,'2018-03-20',0,'',0,0,0,'2018-03-20','2018-03-20',1,0,1,1,1,38.25,0.00,0.00,1,83),(47,127.50,'2018-01-01',0.00,'2018-04-01',1,'',0,0,0,'2018-01-09','2018-04-01',0,0,0,0,1,127.50,127.50,0.00,1,80);
    
asked by anonymous 04.04.2018 / 03:35

0 answers