I'm triying to add quantities of fuels from two tables.

• Payments table contains data about customers and dates.
• Fuels table contains data about fuels recharged.

I need to add the litres by customer in 3 groups:

1. from yesterday until 46 days
2. 47 days ago until 91
3. 366 days ago until 410

Then I created this code:

The structure of the tables are:

TABLE payments

TABLE fuels

SELECT payments.customer,   
       SUM(CASE 
                WHEN ((TIMESTAMPDIFF(DAY, payments.date, now()) > 1) AND (TIMESTAMPDIFF(DAY, payments.date, now()) <= 46))
                THEN fuels.litres 
                ELSE 0 
           END) AS "Quantity_d",
       SUM(CASE 
                WHEN ((TIMESTAMPDIFF(DAY, payments.date, now()) > 46) AND (TIMESTAMPDIFF(DAY, payments.date, now()) <= 91))
                THEN fuels.litres 
                ELSE 0 
           END) AS "Quantity_d2",
       SUM(CASE 
                WHEN ((TIMESTAMPDIFF(DAY, payments.date, now()) > 365) AND (TIMESTAMPDIFF(DAY, payments.date, now()) <= 410))
                THEN fuels.litres 
                ELSE 0 
           END) AS "Quantity_d3"
FROM payments
INNER JOIN fuels ON fuels.id=payments.id
WHERE payments.date > ADDDATE(now(),-411)
GROUP BY customer

The result is that Quantity_d1 is adding from yesterday until day 410, Quantity_d2 from 45 days ago until 410 and Quantity_d3 from 365 days ago until 410.

What am I doing wrong?

Thanks

CodePudding user response：

Without knowing the table structures and relationships, I do find it odd that you're joining on payments.id and fuels.id as those both look like primary key fields. Is it possible that you are joining the wrong id fields, e.g. fuels.id = payments.fuel_id?

CodePudding user response：

If I run your SQL without changes I get exactly what is specified:

If you still have issues, please explain!