How to use partition by instead of group by?
Hi,
I am having trouble using partition by clause in following case,
column other_number with null values contains 10 records in 'some_table'
5 records with date 11-01-2009, item_code = 1
5 records with date 10-01-2009, item_code = 2
This query returns all 10 records, (which suppose to return 2)
SELECT count (a.anumber) over (partition by TO_char(a.some_date,'MM'), a.item_code) AS i_count, a.item_code,
TO_char(a.some_date,'MM')
FROM some_table
WHERE to_char(a.some_date,'yyyy') = 2009
AND a.other_number IS NULL
Works fine if I wrote like this,
SELECT count (a.anumber) AS i_count, a.item_code,
TO_char(a.some_date,'MM')
FROM some_table
WHERE to_char(a.some_date,'yyyy') = 2009
AND a.other_number IS NULL
group by TO_char(a.some_date,'MM'), a.item_code
How to use partition by in this case?