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);