consult and add BD result

Asked

Viewed 43 times

0

I have a table (dia) receiving the data during the 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 an appointment regarding the current day ($date), separate Paid Services (fdp) with D (money) or C (Card) and add them up.

Would there be any simple way to make this sum? Without weighing the code too much because I will have to make a second query to do the same with Products.

php mysqli

1 answer

2


Hello, I understand you want to do an 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’m using Mysql so in another database the syntax can change a little.

1) Creation of table in 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) Carrying out the consultation

    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) Result of consultation: inserir a descrição da imagem aqui

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

Browser other questions tagged php mysqli

You are not signed in. Login or sign up in order to post.