ORACLE SQL Help Needed with EXTRACT and PARTION and GROUP BY

move OVER (PARTION BY 1,2)  to the end ...

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' 
;

Select allOpen 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.

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.

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

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...

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.

Sample input data would help a lot.

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.

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.