consult and add DB result

Navigation
0

I have a table ( dia ) that receives the data over the course of days: 

(id, dia, cliente, func, servicos, valor, produtos, valorp, fdp)
(1, 2018-11-01, cliente1, func1, servicos1, 0++, produto1, 0++, D ou C)

I need to make a query for the current day ( $date ), separate Paid Services ( fdp ) with D (money) or C (Card) and add them.

Would you have some simple way to make that sum? Without much regret the code because I will have to make a second query to do the same with Products.

    
asked by anonymous 07.11.2018 / 05:51

1 answer

2

Hello, I understand that you want to do a SQL query. If this is the case follow the steps with an example database that I created for you to test, in this case I am using MySQL so in another database the syntax may change a little.

1) Creating the table in the database 

CREATE TABLE 'DATA' (
  'ID' int(11) NOT NULL AUTO_INCREMENT,
  'DIA' varchar(50) NOT NULL,
  'CLIENTE' varchar(25) NOT NULL,
  'FUNCIONARIO' varchar(25) NOT NULL,
  'SERVICO' varchar(25) NOT NULL,
  'VALOR' decimal(7,2) DEFAULT NULL,
  'PRODUTOS' varchar(25) NOT NULL,
  'VALORRP' decimal(7,2) DEFAULT NULL,
  'FDP' char(1) DEFAULT NULL,
  PRIMARY KEY ('ID')
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2) Populating the table 

INSERT INTO 'DATA' ('ID', 'DIA', 'CLIENTE', 'FUNCIONARIO', 'SERVICO', 'VALOR', 'PRODUTOS', 'VALORRP', 'FDP')
VALUES
    (1,'10/11/2018','CLIENTE1','FUNCIONARIO1','SERVICO1',22.00,'PRODUTOS1',25.00,'D'),
    (2,'11/11/2018','CLIENTE2','FUNCIONARIO1','SERVICO1',10.00,'PRODUTOS2',16.00,'D'),
    (3,'11/11/2018','CLIENTE2','FUNCIONARIO1','SERVICO1',22.00,'PRODUTOS2',12.00,'C'),
    (4,'11/11/2018','CLIENTE2','FUNCIONARIO2','SERVICO2',11.00,'PRODUTOS2',16.00,'D'),
    (5,'11/11/2018','CLIENTE1','FUNCIONARIO1','SERVICO2',11.00,'PRODUTOS2',13.00,'D'),
    (6,'11/11/2018','CLIENTE2','FUNCIONARIO2','SERVICO2',11.00,'PRODUTOS2',16.00,'D'),
    (7,'12/11/2018','CLIENTE1','FUNCIONARIO1','SERVICO2',11.00,'PRODUTOS2',13.00,'D'),
    (8,'12/11/2018','CLIENTE2','FUNCIONARIO2','SERVICO2',13.00,'PRODUTOS2',12.00,'C'),
    (9,'13/11/2018','CLIENTE2','FUNCIONARIO2','SERVICO2',11.00,'PRODUTOS2',16.00,'D'),
    (10,'13/11/2018','CLIENTE2','FUNCIONARIO1','SERVICO2',10.00,'PRODUTOS2',13.00,'D');

3) Conducting the query 

    SELECT
    DIA,
    SUM(case when FDP = 'D' then VALOR else 0 end) AS DEBITO,
    SUM(case when FDP = 'C' then VALOR else 0 end) AS CREDITO,
    (SUM(case when FDP = 'D' then VALOR else 0 end) + SUM(case when FDP = 'C' then VALOR else 0 end) ) as TOTAL
    FROM DATA WHERE DIA = '11/11/2018' GROUP BY DIA

4) Outcome of the consultation:

If you want to search only the current day then the where you will be DAY = CURDATE ()

    
07.11.2018 / 09:29
Display an alert box when loading page Submit being called twice