Query to sum total query

Question

Query to sum total query

Navigation

#1 by (3 votes)

2

I have a Query to select a user list with debits I have to make the sum of these debits.

SQL

/*
* PAGAMENTO
*/

SELECT  
  pe.name as nomepessoa,
  py.created_at as datapagamento,
  py.amount_paid as pagamento,
  pys.name as status
FROM 
  payments as py, 
  payment_status as pys, 
  people as pe 
WHERE 
  pe.id = py.person_id AND pys.id = py.payment_status_id AND
  py.created_at < CURRENT_DATE AND py.created_at > CURRENT_DATE -199
GROUP BY 
  nomepessoa, datapagamento, pagamento, status
ORDER BY
  nomepessoa

sql postgresql

asked by anonymous 27.10.2017 / 22:35

1 answer

Check if reCAPTCHA has been marked Read a specific value in Json with PHP

score 3 · Accepted Answer

Assuming your table structure and data are something like:

SET DATESTYLE = DMY;

CREATE TABLE payments
(
    person_id INTEGER,
    payment_status_id  INTEGER,
    amount_paid REAL,
    created_at TIMESTAMP
);

CREATE TABLE people
(
    id INTEGER,
    name TEXT
);

CREATE TABLE payment_status
(
    id INTEGER,
    name TEXT
);

INSERT INTO people ( id, name ) VALUES ( 1, 'JUDAS ESCARIOTES' );
INSERT INTO people ( id, name ) VALUES ( 2, 'MARIA MADALENA' );
INSERT INTO people ( id, name ) VALUES ( 3, 'JESUS DE NAZARE' );

INSERT INTO payment_status ( id, name ) VALUES ( 1, 'EM PROCESSAMENTO' );
INSERT INTO payment_status ( id, name ) VALUES ( 2, 'A PAGAR' );
INSERT INTO payment_status ( id, name ) VALUES ( 3, 'PAGO' );

-- JUDAS
INSERT INTO payments ( person_id, payment_status_id, amount_paid, created_at ) VALUES ( 1, 1, 30.00, '19.10.2017'  );
INSERT INTO payments ( person_id, payment_status_id, amount_paid, created_at ) VALUES ( 1, 1, 130.50, '19.12.2017'  );
INSERT INTO payments ( person_id, payment_status_id, amount_paid, created_at ) VALUES ( 1, 2, 100.10, '01.10.2017' );
INSERT INTO payments ( person_id, payment_status_id, amount_paid, created_at ) VALUES ( 1, 2, 33.95, '19.08.2017'  );
INSERT INTO payments ( person_id, payment_status_id, amount_paid, created_at ) VALUES ( 1, 3, 100.00, '01.07.2017' );

-- MARIA
INSERT INTO payments ( person_id, payment_status_id, amount_paid, created_at ) VALUES ( 2, 1, 13.50, '22.09.2017' );
INSERT INTO payments ( person_id, payment_status_id, amount_paid, created_at ) VALUES ( 2, 2, 300.00, '10.07.2017' );
INSERT INTO payments ( person_id, payment_status_id, amount_paid, created_at ) VALUES ( 2, 3, 34.75, '01.06.2017'  );
INSERT INTO payments ( person_id, payment_status_id, amount_paid, created_at ) VALUES ( 2, 3, 120.00, '10.07.2017' );

-- JESUS
INSERT INTO payments ( person_id, payment_status_id, amount_paid, created_at ) VALUES ( 3, 3, 13.50, '02.07.2017' );
INSERT INTO payments ( person_id, payment_status_id, amount_paid, created_at ) VALUES ( 3, 2, 70.00, '01.07.2017'  );
INSERT INTO payments ( person_id, payment_status_id, amount_paid, created_at ) VALUES ( 3, 1, 123.00, '06.08.2017' );
INSERT INTO payments ( person_id, payment_status_id, amount_paid, created_at ) VALUES ( 3, 2, 150.10, '22.01.2017' );

Your query would look like this:

SELECT  
    pe.name as nomepessoa,
    pys.name as status,
    SUM(py.amount_paid) as total
FROM 
    payments AS py
JOIN
    payment_status as pys ON ( pys.id = py.payment_status_id )
JOIN
    people as pe ON ( pe.id = py.person_id ) 
WHERE 
    py.created_at BETWEEN CURRENT_DATE - '199 days'::interval AND CURRENT_DATE
GROUP BY 
     nomepessoa,
     status
ORDER BY
     nomepessoa,
     status;

Output:

Seehowitworksin SQLFiddle .