CODEWITHSUNDEEP
mysqlsql
tatorface
tatorface

Reputation: 72

Need to sum transaction totals from one table using customer information in another

I have spent the last hour looking for something I can use to implement here, but haven't found exactly what I need.

I have 2 tables: TRANSACTIONS & CUSTOMERS

CUSTOMER
internal_id | name | email

TRANSACTIONS
internal_id | customer_id | transaction_date | total_amount

I would like to cycle through all CUSTOMERS, then sum up the total TRANSACTIONS for each by month and year. I thought it would be as easy as just adding select statements as columns to the initial query, but that isn't working obviously:

NOT WORKING:

select customer.internal_id, 
(sum(total_amount) as 'total' from TRANSACTIONS where transactions.customer_id = customer.internal_id and transaction_date  >= DATE_SUB(NOW(),INTERVAL 1 month)), 
 (sum(total_amount) as 'total' from TRANSACTIONS where transactions.customer_id = customer.internal_id and transaction_date  >= DATE_SUB(NOW(),INTERVAL 1 year))
from CUSTOMER join TRANSACTIONS on CUSTOMER.internal_id = TRANSACTIONS.customer_id

Basically I would like the output to look like this:

CUSTOMER.name | TRANSACTIONS.total_amount_month | TRANSACTIONS.total_amount_year
ABC Company   | $335.00                         | $8900.34

Is this possible with a single query? I have it implemented with multiple queries using PHP and would just prefer a single query if possible for performance sake.

Thanks!

Upvotes: 0

Views: 96

Answers (1)

Barmar
Barmar

Reputation: 781974

SELECT c.name,
       SUM(IF(transaction_date >= DATE__SUB(NOW(), INTERVAL 1 MONTH), total_amount, 0) AS total_amount_month,
       SUM(total_amount) AS total_amount_year
FROM transactions AS t
JOIN customer AS c ON c.internal_id = t.customer_id
WHERE transaction_date >= DATE__SUB(NOW(), INTERVAL 1 YEAR
GROUP BY t.customer_id

Upvotes: 1

Related Questions