Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Rohit.

Asked: January 12, 2018 - 7:05 pm UTC

Last updated: January 15, 2018 - 9:15 am UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

Hi Tom,

I am trying to get YTD in a view. I have below view,

create or replace view billsummary as
select  szRegionCode,
        szState,
  szPartitionCode,
  szProduct,
  TO_CHAR(dtSnapshot,'YYYY.MM') szMonthYear,
  szJioCenter,
  dtSnapshot,
  szCustomerCategory,
  iCycleDay,
  fbillamt,
  ibillcnt
  sum(CRBC.fbillamt) over (partition by szRegionCode, szState, CRBC.szProduct, TO_CHAR(CRBC.dtSnapshot,'YYYY') , CRBC.szCustomerCategory, iCycleDay
  order by partition by szRegionCode, szState, CRBC.szProduct, CRBC.dtSnapshot , CRBC.szCustomerCategory, iCycleDay) fytdbillamt
  sum(CRBC.ibillcnt) over (partition by szRegionCode, szState, CRBC.szProduct, TO_CHAR(CRBC.dtSnapshot,'YYYY') , CRBC.szCustomerCategory, iCycleDay
  order by partition by szRegionCode, szState, CRBC.szProduct, CRBC.dtSnapshot , CRBC.szCustomerCategory, iCycleDay) iytdBillcnt
From BillCollection;


Suppose table BillCollection has data from Jan 2017 to Dec 2017. If I execute below sql for Dec 2017 month, it should give me sum of all bill amount generated in December month(MTD) and sum of all bill amount generated from Jan 2017 to Dec 2017(YTD). But, YTD figure is not matching at all. Can you please guide me how can I code above mentioned view?

Select szMonthYear, szregioncode, sum(fbillamt) MTD , sum (fytdbillamt) YTD
From billsummary
order by szMonthYear

and Connor said...

Hmmm..... something wrong the view here before we even get started

sum(CRBC.fbillamt) over (partition by ... order by partition by ...) fytdbillamt


because that is not valid syntax, eg

SQL> with t as ( select 1 x, 2 y, 3 z from dual)
  2  select sum(x) over ( partition by y order by partition z )
  3  from t;
select sum(x) over ( partition by y order by partition z )
                                                       *
ERROR at line 2:
ORA-00907: missing right parenthesis


Without trying to make too many assumptions, its important to realise that a view will respect view *definition* first, so a view that does:

create view V as
select ...,
       sum(x) over () as blah


would have column 'blah' being the sum calculated across ALL rows in the table. So even if you then did:

select * From V where name = 'Jones'

then the value that comes back from 'blah' will STILL be the total for ALL rows, not just those rows where name = 'BLAH'


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Rohit Chavan, January 14, 2018 - 1:06 pm UTC

Thanks Connor for your suggestion! I made code changes but still problem persists for YTD. My problem is still unaddressed.

Actually I did a mistake in my original question.

I am correcting my question here,

My report output sql would be like,

Select szMonthYear, szregioncode, sum(fbillamt) MTD , sum (fytdbillamt) YTD
From billsummary
group by szMonthYear, szregioncode
order by szMonthYear


the above sql suppose to give me, Month wise, Reason wise, MTD bill amount and YTD bill amount upto respective month.

I would really appreciate your expert advise on this.

Thanks,
Rohit
Connor McDonald
January 15, 2018 - 9:15 am UTC

Please give us a test cast that *we* can run, top to bottom, which

- creates a simple table
- loads it with sample data via insert statements
- shows what the desired output is for that data
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.