All Forums Database
Mystical 9 posts Joined 12/10
02 Dec 2010
Count and Sum by Month

I need to summarise some records for this year, by the number of entries and their value.Split by month. The issue i have is that each entry is listed by date and not by month. Could somebody please advise me how i need to amend the querey so as to summarise by calender month.

With thanks

select
count ( mi_amount),
sum (mi_amount),
payment_date

from

U_UKU_2010
where payment_date ge '2010/01/01'

group by payment_date desc

Dixxie 58 posts Joined 12/10
02 Dec 2010

Hi,
If you need the totals grouped by year+month try this:

Select
extract(year from payment_date) || extract(month from payment_date) as payment_month,
Count (mi_amount),
Sum (mi_amount),
From
U_UKU_2010
Where payment_date ge '2010/01/01'
Group by extract(year from payment_date) || extract(month from payment_date) desc

BR
Dixxie

s@ir@m 35 posts Joined 05/13
23 Jan 2014

hi all,
 
i have a transaction table columns like 
T_ID,T_DATE,T_AMOUNT .
 
Q : i need amount from date wise,and t_amount wise like bellow . 
SELECT T_ID,JAN_T_AMOUNT,FEB_T_AMOUNT,MAR_T_AMOUNT 
is it possible ?.
send me ASAP
 

Raja_KT 1246 posts Joined 07/09
23 Jan 2014

Hi,
Do you mean something  like this?

SUM(CASE WHEN EXTRACT (MONTH FROM T_DATE) =1 THEN t_amount ELSE 0 END) AS JAN_T_AMOUNT

SUM(CASE WHEN EXTRACT (MONTH FROM T_DATE) =2 THEN t_amount ELSE 0 END) AS FEB_T_AMOUNT

........

 

Cheers,

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

s@ir@m 35 posts Joined 05/13
23 Jan 2014

Hi raja,
 
like that but 
i want retrive data result set is like  id,jan_Amount,Feb_amount.....etc

You must sign in to leave a comment.