ORACLE SQL Help Needed with EXTRACT and PARTION and GROUP BY

Just learned about EXTRACT and I am trying to put together a query of the number of a particular file type stored each month.

select extract(month from datestored),  extract(YEAR from datestored) , Count(unique_id) cnt
OVER (PARTION BY 1,2) from
emrcontent.indexdata@prod where substr(filename,-3) = 'OID'
RETURNS ERROR...

Error starting at line 1 in command:
select extract(month from datestored),  extract(YEAR from datestored) , Count(unique_id) cnt  
OVER (PARTION BY 1,2) from
emrcontent.indexdata@prod where substr(filename,-3) = 'OID' and rownum < 10  
Error at Command Line:1 Column:94
Error report:
SQL Error: ORA-00923: FROM keyword not found where expected
00923. 00000 -  "FROM keyword not found where expected"

Any help appreciated. Thanks in advance.


codefingerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

elimesikaCommented:
move OVER (PARTION BY 1,2)  to the end ...
0
Kevin CrossChief Technology OfficerCommented:
Try like this:
SELECT EXTRACT(month FROM datestored) AS "month"
     , EXTRACT(year FROM datestored) AS "year" 
	 , COUNT(unique_id) OVER (PARTITION BY 1,2) AS "cnt"
FROM "emrcontent"."indexdata@prod" 
WHERE SUBSTR(filename, -3) = 'OID' 
;

Open in new window


I would have to test in Oracle, but in MS SQL OVER(PARTITION BY 1, 2) treats those as literals and not column indexes, so it pretty much the same results as OVER(). Other than that, I think you just had the alias "cnt" in the wrong spot.
0
Kevin CrossChief Technology OfficerCommented:
I just did some tests in Oracle and at least in 10g, the behavior is the same as MS SQL; therefore, you may want to relook at the OVER(PARTITION BY 1,2). I could also duplicate exact error message by misplacing the alias. I additionally got that error if I used wildcard (*) without specifying table, then did a COUNT() OVER() even if I had the alias in the right place. So just some additional causes to help you in the future, but think the culprit was the "Count(unique_id) cnt OVER (PARTION BY 1,2)" in this case.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

slightwv (䄆 Netminder) Commented:
Why use extract and OVER at all?

select to_char(datestored,'MM') month, to_char(datestored,'YYYY') year, count(*)
from emrcontent.indexdata@prod where substr(filename,-3) = 'OID'
group by to_char(datestored,'MM'), to_char(datestored,'YYYY');
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kevin CrossChief Technology OfficerCommented:
I was wondering that same thing too last night Steve and did not get to post back. It does appear to be an attempt to get COUNT() by Year/Month combination. At least that is what my brain started to tell me when I figured 1, 2 where trying to refer to the EXTRACT() columns without having to retype it all out, but as you showed that IS what is required, i.e., you have to repeat the formula in the GROUP BY. Using OVER() way works better if what is needed is detail rows in addition to the aggregate over a specific grouping. My gut told me this scenario is probably the simpler one shown here -- http:#36924051
0
codefingerAuthor Commented:
Guys, thanks for your help so far....this is what I want my report to look like:

MONTH  YEAR  COUNT
-----------------------------------
12          2004      120
01          2005      145
02          2005      185
03          2005      262

I may be on completely the wrong track, so I am open to more suggestions.  What I have received so far does not quite do it, clearly because I did not provide enough detail in the original question.  I apologize for that and I hope this comment adequately corrects that over-site...

0
Kevin CrossChief Technology OfficerCommented:
Can you share what is different from what you want and what you get with slightwv's query...that may help us understand what is going on; however, aside from alias on count, his query should do exactly what you showed.
0
slightwv (䄆 Netminder) Commented:
Sample input data would help a lot.
0
codefingerAuthor Commented:
Actually, mwvsiat IS on target.  I made some mistake on my end when I tried first tried his query (not sure what), but it seems to be working quite well this morning once I added an order by clause (and a date limitation since there are 12 million records in this database and I am just testing now):

select to_char(datestored,'MM') month, to_char(datestored,'YYYY') year, count(*)
from emrcontent.indexdata@prod where substr(filename,-3) = 'OID' and datestored < to_date('12/31/2006','MM/DD/YYYY')
group by to_char(datestored,'MM'), to_char(datestored,'YYYY')
order by year,month;

....gives me:


MONTH YEAR COUNT(*)              
----- ---- ----------------------
12    2004 133                    
01    2005 231                    
02    2005 257                    
03    2005 908                    
04    2005 626                    
05    2005 1233

However, these counts do not quite agree with what I get when I run a query that looks like this:
SELECT COUNT(*) FROM EMRCONTENT.INDEXDATA WHERE SUBSTR(FILENAME,-3) = 'OID'  AND DATESTORED >= TO_DATE(:xfromdate,'MM/DD/YYYY') AND DATESTORED <= TO_DATE(:xtodate,'MM/DD/YYYY')

...and use beginning and end of the month as the from and to dates.

My eventual goal is to convert each group of files based on dates supplied by the user in a textbox, I want to be sure none are missed.   The datestored value is an Oracle timedate stamp.

Thanks again for your help so far.  I appreciate the help and the patience.









0
slightwv (䄆 Netminder) Commented:
The to_date as written in the second query defaults the time piece to: 00:00:00

Any records for thw last day of the month aftwr midnight will not be counted.  You are missing a days records.
0
codefingerAuthor Commented:
This was supposed to be the Assisted Solution:

slightwv:
------------------
The to_date as written in the second query defaults the time piece to: 00:00:00
Any records for thw last day of the month aftwr midnight will not be counted.  You are missing a days records.
...

and in one comment I said
--------------------------------------
Actually, mwvsiat IS on target  

 -- that was a typo, I meant to say
------------------------------------------
slightwv IS on target,
 
I was actually agreeing with mwvsiat.

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.