Skip to Main Content
  • Questions
  • Query to retrieve month wise total amount for a given year

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Joohi.

Asked: March 12, 2020 - 2:59 pm UTC

Answered by: Chris Saxon - Last updated: March 13, 2020 - 4:23 pm UTC

Category: PL/SQL - Version: 10.1.2

Viewed 1000+ times

You Asked

Hi There,

I have a requirement to retrieve month wise total amount for a given year in below format-


Org  Jan'18 Feb'18 Mar'18
a $100 $200 $111
b $222 $333 $100



Earlier I achieved a count of rows for the same period but I am not sure how to get the total amount using case statement?

select org_name
count(case to_char(A.creation_date,'YYYY-MM') when '2018-06' then 1  end)"Jun-18 ",
count(case to_char(A.creation_date,'YYYY-MM') when '2018-07' then 1  end)"Jul-18 ",
count(case to_char(A.creation_date,'YYYY-MM') when '2018-08' then 1  end)"Aug-18 ",
count(case to_char(A.creation_date,'YYYY-MM') when '2018-09' then 1  end)"Sep-18 ",
count(case to_char(A.creation_date,'YYYY-MM') when '2018-10' then 1  end)"Oct-18 ",
count(case to_char(A.creation_date,'YYYY-MM') when '2018-11' then 1  end)"Nov-18 ",
count(case to_char(A.creation_date,'YYYY-MM') when '2018-12' then 1  end)"Dec-18 ",
count(case to_char(A.creation_date,'YYYY-MM') when '2019-01' then 1  end)"Jan-19 ",
count(case to_char(A.creation_date,'YYYY-MM') when '2019-02' then 1  end)"Feb-19 ",
count(case to_char(A.creation_date,'YYYY-MM') when '2019-03' then 1  end)"Mar-19 ",
count(case to_char(A.creation_date,'YYYY-MM') when '2019-04' then 1  end)"Apr-19 ",
count(case to_char(A.creation_date,'YYYY-MM') when '2019-05' then 1  end)"May-19 "
from table1 a,table2 etl
where  a.ORG_ID             = etl.org_id
AND etl.LANGUAGE='US'
and etl.effective_end_date> sysdate
group by etl.org_name


and we said...

If you want to add up the totals for each month, just change the counts to sums of the value:

select org_name,
  sum(case to_char(A.creation_date,'YYYY-MM') when '2018-06' then val end)"Jun-18 ",
  sum(case to_char(A.creation_date,'YYYY-MM') when '2018-07' then val end)"Jul-18 ",
  sum(case to_char(A.creation_date,'YYYY-MM') when '2018-08' then val end)"Aug-18 ",
  sum(case to_char(A.creation_date,'YYYY-MM') when '2018-09' then val end)"Sep-18 ",
  ...
from table1 a,table2 etl
where a.ORG_ID = etl.org_id
AND etl.LANGUAGE='US'
and etl.effective_end_date> sysdate
group by etl.org_name;


Or if you're using a modern version of Oracle Database, switch to the pivot clause:

with rws as (
  select org_name, to_char(A.creation_date,'YYYY-MM') dt, val
  from   table1 a,table2 etl
  where a.ORG_ID = etl.org_id
  AND   etl.LANGUAGE='US'
  and   etl.effective_end_date> sysdate
)
  select * from rws
  pivot (
    sum ( val ) for dt in (
      '2018-06' "Jun-18", '2018-07' "Jul-18", '2018-08' "Aug-18",
    )
  )


Read more about this at https://blogs.oracle.com/sql/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot

and you rated our response

  (3 ratings)

Reviews

March 12, 2020 - 3:50 pm UTC

Reviewer: A reader

Thanks Chris for your reply.
I would use Pivot as Sum not working as expected. It shows the same results as Count data.
I have to also display the amount in USD so I have to join gl_daily_rates a, gl_daily_conversion_types b tables for conversion. So how can I achieve that?

March 12, 2020 - 4:24 pm UTC

Reviewer: A reader

I tried to modify the query with this but not working. Can you please help ?

with rws as (
select org_name, to_char(er.creation_date,'YYYY-MM') dt, ER_TOTAL,
DECODE(er.reim_curr_code,'USD',NVL(ER_TOTAL,0),(select (ER_TOTAL)*(round(MAX(a.conversion_rate),2))
from gl_daily_rates a, gl_daily_conversion_types b
where to_currency = 'USD'
and a.conversion_type = b.conversion_type
and b.user_conversion_type = 'Treasury'
and TRUNC(conversion_date) = TRUNC(A.creation_date)
and er.reimbursement_currency_code=FROM_CURRENCY
GROUP BY a.conversion_type,to_currency))total_USD
from table1 er,table2 etl
where er.ORG_ID = etl.organization_id
AND etl.LANGUAGE='US'
and etl.effective_end_date> sysdate
)
select * from rws
pivot (
sum( ER_TOTAL) for dt in
('2018-06' "Jun-18", '2018-07' "Jul-18", '2018-08' "Aug-18",'2018-09' "Sep-18", '2018-10' "Oct-18",'2018-11' "Nov-18",'2018-12' "Dec-18",'2019-01' "Jan-19",'2019-02' "Feb-19",
'2019-03' "Mar-19",'2019-04' "Apr-19",'2019-05' "May-19")
)
Chris Saxon

Followup  

March 13, 2020 - 11:36 am UTC

Hmmm, that query is a lot more complex than originally suggested...

Please provide example data (create table + inserts) that shows the problem and the results you want based on these data.

March 13, 2020 - 1:33 pm UTC

Reviewer: A reader

I have to convert all the amount in USD so using gl_Daily_rates table to join it but query is very slow now due to this.
This query works fine for 1 or 2 BU but if I run it for all BUs then it gives me timeout error-
Is there any possibility to tune below query?

with rws as (
select org_name, to_char(er.creation_date,'YYYY-MM') dt,
DECODE(er.reim_curr_code,'USD',NVL(ER_TOTAL,0),(select (ER_TOTAL)*(round(MAX(a.conversion_rate),2))
from gl_daily_rates a, gl_daily_conversion_types b
where to_currency = 'USD'
and a.conversion_type = b.conversion_type
and b.user_conversion_type = 'Treasury'
and TRUNC(conversion_date) = TRUNC(A.creation_date)
and er.reimbursement_currency_code=FROM_CURRENCY
GROUP BY a.conversion_type,to_currency))total_USD
from table1 er,table2 etl
where er.ORG_ID = etl.organization_id
AND etl.LANGUAGE='US'
and etl.effective_end_date> sysdate
)
select * from rws
pivot (
sum( total_USD) for dt in
('2018-06' "Jun-18", '2018-07' "Jul-18", '2018-08' "Aug-18",'2018-09' "Sep-18", '2018-10' "Oct-18",'2018-11' "Nov-18",'2018-12' "Dec-18",'2019-01' "Jan-19",'2019-02' "Feb-19",
'2019-03' "Mar-19",'2019-04' "Apr-19",'2019-05' "May-19")
)
Chris Saxon

Followup  

March 13, 2020 - 4:23 pm UTC

As stated in my previous reply, please give us a complete test case:

- Create tables
- Insert intos
- Expected output based on these data

As you've asked about performance, please also supply the execution plan NOT EXPLAIN for the query.

Get this using:
set serveroutput off
select /*+ gather_plan_statistics */* from ...
select * 
from   table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.