• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 443
  • Last Modified:

MYSQL sum: Multiple sums with different criteria

I am using MySQL 4 (no VIEWS)
I have a long, complex query of invoices that returns a table of:
INVOICE DATE, INVOICE AMOUNT

Now, I want to create a few sums ALL IN ONE QUERY:
- A sum of the revenue of these invoices from the last 3 months.
- A sum of the revenue of these invoices for the last 6 months.
- A sum of the revenue of these invoices for the last 10 months.

I _could_ just copy and paste this rediculously long query, but I'm sure there's a better way.

Here's the Query that produces a table of invoices dates paid and the amount:
[Keep in mind that tdate just has '2008-03-01 12:00:00']

SELECT cust.invoice_date_paid, cust.invoice_amount_paid
FROM
cust,
cust_package,
cust_info,
package_type
WHERE
cust.cust_id=cust_info.cust_id AND
cust_info.cust_id=cust_package.cust_id AND
cust_package.pack_id=package_type.pack_id AND
cp_start_stamp > UNIX_TIMESTAMP((SELECT tdate FROM TempDel)) AND
cp_start_stamp < UNIX_TIMESTAMP(Date_add( (SELECT tdate FROM TempDel), INTERVAL 1 month));
0
the_cyman
Asked:
the_cyman
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you can, using the CASE statement:
SELECT SUM(CASE WHEN INVOICE_DATE >= DATE_SUB(NOW, INTERVAL 3 MONTH) THEN INVOICE_AMOUNT ELSE 0 END) REVENUE_LAST_3_MONTH
, SUM(CASE WHEN INVOICE_DATE >= DATE_SUB(NOW, INTERVAL 6 MONTH) THEN INVOICE_AMOUNT ELSE 0 END) REVENUE_LAST_6_MONTH
, SUM(CASE WHEN INVOICE_DATE >= DATE_SUB(NOW, INTERVAL 10 MONTH) THEN INVOICE_AMOUNT ELSE 0 END) REVENUE_LAST_10_MONTH
FROM yourtable
WHERE INVOICE_DATE >= DATE_SUB(NOW, INTERVAL 10 MONTH) 

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now