[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MYSQL sum: Multiple sums with different criteria

Posted on 2008-11-09
1
Medium Priority
?
438 Views
Last Modified: 2012-05-05
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
Comment
Question by:the_cyman
1 Comment
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 22916930
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

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month20 days, 9 hours left to enroll

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question