471,108 Members | 1,291 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,108 software developers and data experts.

Two alike conditions in query. Is it possible?

Hello, All!
Example. I have a table with information about payments (id, amount,
pay_date, customer_id). Can I select two sums for two different date ranges,
grouped by 'customer_id' (for each customer) in one query?

--
With best regards, Gregory Konev. E-mail: vo****@ml.net.ua
Jul 23 '05 #1
2 1390
Gregory Konev wrote:
Example. I have a table with information about payments (id, amount,
pay_date, customer_id). Can I select two sums for two different date ranges,
grouped by 'customer_id' (for each customer) in one query?


Not as fields of a single record in the result set. But there are a
couple of ways you could get the two sums in separate records.

You could use UNION to combine two separate queries, and then sort them
together by the customer_id. See example of using ORDER BY over two
unioned queries at http://dev.mysql.com/doc/mysql/en/union.html

You could also GROUP BY both the customer_id and an expression on the date:

SELECT t.customer_id, SUM(t.amount),
CASE WHEN t.pay_date BETWEEN '2003-01-01' AND '2003-12-31' THEN 'FY2003'
WHEN t.pay_date BETWEEN '2004-01-01' AND '2004-12-31' THEN 'FY2004'
WHEN t.pay_date BETWEEN '2005-01-01' AND '2005-12-31' THEN 'FY2005'
END AS `date_range`
FROM t
GROUP BY t.customer_id, date_range;

Regards,
Bill K.
Jul 23 '05 #2
Gregory Konev (vo****@ml.net.ua) wrote:
: Hello, All!
: Example. I have a table with information about payments (id, amount,
: pay_date, customer_id). Can I select two sums for two different date ranges,
: grouped by 'customer_id' (for each customer) in one query?

select data from both date ranges grouped by customer id.

The question then becomes what value is being summed for each sum.

The value inside the sum must be a calculation that checks the date. If
the date is in the range then use the value, else use 0.

pseudo code

select
sum(
if date_column between start1 and end1
then COLUMN_BEING_SUMMED
else 0
) as sum_from_range_1 ,
sum(
if date_column between start2 and end2
then COLUMN_BEING_SUMMED
else 0
) as sum_from_range_2
from
the_table
group by id
mysql has functions like IF and various date functions, but I don't use
mysql enough to simply show you which ones to use here. You'll have to
look that up.


--

This space not for rent.
Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Pedro Alves | last post: by
4 posts views Thread by ime | last post: by
2 posts views Thread by Igor | last post: by
6 posts views Thread by jackal_on_work | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.