"ORA-00923: FROM keyword not found where expected"

Question

I am getting the following error

"ORA-00923: FROM keyword not found where expected" while executing the query

SELECT 
    count(pf.pref_selection) filter ( WHERE pc.collection = "players")      OVER (partition BY pc.collection,pc.user_id )  AS Avg_players        
    count(pf.pref_selection) filter ( WHERE pc.collection = "teams")        OVER (partition BY pc.collection,pc.user_id )  AS Avg_teams  
FROM NBA_OWNER.PREFERENCES pf 
inner join NBA_OWNER.PREF_COLLECTIONS pc on pc.ID=pf.COLLECTION_ID 
where pc.LAST_UPDATE_DATE>"30-SEP-16'`

Answer 1

You have several errors. As @cricket_007 pointed put you are missing a comma between the two count expressions. But you also have invalid syntax with your filter clause - which doesn't exist, at least in Oracle; you have used double quotes around string literals instead of single quotes; the quotes around your date string are even odder; and you are relying on NLS settings for the date format.

This is closer to what you need, if I understand what you were attempting:

SELECT 
    count(case when pc.collection = 'players' then pf.pref_selection end)
      OVER (partition BY pc.user_id) AS Avg_players, 
    count(case when pc.collection = 'teams' then pf.pref_selection end)
      OVER (partition BY pc.user_id) AS Avg_teams  
FROM NBA_OWNER.PREFERENCES pf 
inner join NBA_OWNER.PREF_COLLECTIONS pc on pc.ID=pf.COLLECTION_ID 
where pc.LAST_UPDATE_DATE > date '2016-09-30'

The output might not be very useful though. For each user ID you may get multiple rows, each showing the total count (which you've called avg for some reason). You probably only want to see the counts for each ID once, and to see which ID they relate to, so I think you want an aggregate rather than analytic count:

SELECT 
    pc.user_id,
    count(case when pc.collection = 'players' then pf.pref_selection end)
      AS Avg_players,      
    count(case when pc.collection = 'teams' then pf.pref_selection end)
      AS Avg_teams  
FROM NBA_OWNER.PREFERENCES pf 
inner join NBA_OWNER.PREF_COLLECTIONS pc on pc.ID=pf.COLLECTION_ID 
where pc.LAST_UPDATE_DATE > date '2016-09-30'
group by pc.user_id