Home / Questions / 124152
Accepted
Pablo Lozano
Asked: 2020-12-13 02:43:05 +0800 CST 2020-12-13 02:43:05 +0800 CST

Query by date range with many nested queries

  • 772

I have to obtain a list of the company's clients to whom we have invoiced something during the current year, indicating how much we have invoiced each month.

I have two tables: the customer table (customer) and the invoice table (invoice). The relationship is obvious: a customer can have many invoices, and each invoice is related to a single customer.

The only thing that came to my mind is the following:

select id, 
name as "Nombre",
identity_number as "CIF", 
(select sum(total) 
    from invoice 
    where payment_date between '2017-01-01' and '2017-02-01' and customer_id=c.id group by customer_id) as "Enero",
(select sum(total) 
    from invoice 
    where payment_date between '2017-02-01' and '2017-03-01' and customer_id=c.id group by customer_id) as "Febrero",
(select sum(total) 
    from invoice 
    where payment_date between '2017-03-01' and '2017-04-01' and customer_id=c.id group by customer_id) as "Marzo",
(select sum(total) 
    from invoice 
    where payment_date between '2017-04-01' and '2017-05-01' and customer_id=c.id group by customer_id) as "Abril",
(select sum(total) 
    from invoice 
    where payment_date between '2017-05-01' and '2017-06-01' and customer_id=c.id group by customer_id) as "Mayo",
(select sum(total) 
    from invoice 
    where payment_date between '2017-06-01' and '2017-07-01' and customer_id=c.id group by customer_id) as "Junio",
(select sum(total) 
    from invoice 
    where payment_date between '2017-07-01' and '2017-08-01' and customer_id=c.id group by customer_id) as "Julio",
(select sum(total) 
    from invoice 
    where payment_date between '2017-08-01' and '2017-09-01' and customer_id=c.id group by customer_id) as "Agosto",
(select sum(total) 
    from invoice 
    where payment_date between '2017-09-01' and '2017-10-01' and customer_id=c.id group by customer_id) as "Septiembre",
(select sum(total) 
    from invoice 
    where payment_date between '2017-10-01' and '2017-11-01' and customer_id=c.id group by customer_id) as "Octubre",
(select sum(total) 
    from invoice 
    where payment_date between '2017-11-01' and '2017-12-01' and customer_id=c.id group by customer_id) as "Noviembre",
(select sum(total) 
    from invoice 
    where payment_date between '2017-12-01' and '2017-12-31' and customer_id=c.id group by customer_id) as "Diciembre"
from customer c
where c.branding_id=1 and (select sum(total) 
    from invoice 
    where payment_date between '2017-01-01' and '2017-12-31' and customer_id=c.id group by customer_id) > 0

This works, but I find it extremely inefficient (there are 13 subqueries, if we include the one in the WHERE). Is there any way to simplify this query or is it the best way to get the data?

sql

2 Answers

  1. EDIT: after writing this answer (for MySQL) I realized that the question was in PostgreSQL. The correct syntax is in the OP's answer.

    First, the final clause (that the sum of payments from January to December 2017 is greater than zero) is the same as saying that payment_dateit is from the year 2017.

    Second, assuming your invoice table is and contains:

    create table invoice (customer_id integer, total integer, payment_date datetime);
insert into invoice values (1, 5, now());
insert into invoice values (1, 15, NOW() - INTERVAL 1 DAY);
insert into invoice values (1, 45, NOW() - INTERVAL 41 DAY);
insert into invoice values (1, 12, NOW() - INTERVAL 61 DAY);
insert into invoice values (1, 13, NOW() - INTERVAL 91 DAY);
insert into invoice values (2, 55, now());
insert into invoice values (2, 15, NOW() - INTERVAL 21 DAY);
insert into invoice values (3, 145, NOW() - INTERVAL 71 DAY);
insert into invoice values (3, 1, NOW() - INTERVAL 16 DAY);
insert into invoice values (3, 3, NOW() - INTERVAL 121 DAY);

    you can do in each column:

    SUM(IF(MONTH(payment_date)=1, total, 0) as enero

    This would be something like:

    select customer_id,
  SUM(IF(month(payment_date)=12,total,0)) as diciembre,
  SUM(IF(month(payment_date)=11,total,0)) as noviembre,
  SUM(IF(month(payment_date)=10,total,0)) as octubre,
  SUM(IF(month(payment_date)=9,total,0)) as septiembre,
  SUM(IF(month(payment_date)=8,total,0)) as agosto
from invoice
where year(payment_date)=2017
group by customer_id

    enter image description here

    I did not include all the months or the JOIN with the clients table, I leave that part to you, but if you want to list all the clients, including those who did not pay anything this year , FROMit would have to be

    FROM customer c LEFT JOIN invoice on customer_id=c.id AND YEAR(payment_date)=2017

    example fiddle

    • 3
  2. Best Answer

    Based on @amenadiel's answer (which is correct for MySQL), I have created the following query:

    select c.id, 
c.name as "Nombre",
c.identity_number as "CIF", 
  SUM(CASE WHEN date_part('month',i.payment_date)=12 then total else 0 end ) as diciembre,
  SUM(CASE WHEN date_part('month',i.payment_date)=11 then total else 0 end) as noviembre,
  ...
  SUM(CASE WHEN date_part('year',i.payment_date)=2017 then total else 0 end) as TOTAL
from customer c join invoice i on c.id=i.customer_id
where date_part('year',i.payment_date)=2017 and c.branding_id=2
group by c.id
order by c.id

    As a curiosity, the query in the question needed almost two minutes to execute while this solution takes around 300ms to return the same results

    • 1