Two alike conditions in query. Is it possible?

[Gregory Konev]

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

[Bill Karwin]

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.

[Malcolm Dew-Jones]

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.