Count and Sum by Month

Database
Enthusiast

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
4 REPLIES
Enthusiast

Re: Count and Sum by Month

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
Highlighted
s_1
Enthusiast

Re: Count and Sum by Month

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

Enthusiast

Re: Count and Sum by Month

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,

s_1
Enthusiast

Re: Count and Sum by Month

Hi raja,

like that but 

i want retrive data result set is like  id,jan_Amount,Feb_amount.....etc