1. Sum over partition by columns - to have a column that sum all the value from field in the result.
ID Amount Total by ID
1 100 300
1 200 300
3 500 500
Select ID, Amount, Sum Over (Partition by ID) "Total by ID" from Table_Amt
Without this tip, usually we will go for sub query to generate the Total by ID. This trick has really help to cut down lines of codes in sub query with just couple of words.
2. NULLIF to avoid divide by zero error - This function will return NULL if the parameter passed in are matched with argument. Below is an example how it is done to avoid divide by zero error.
Select 100 / NULLIF(Amount, 0) from Table_Amt
Result: NULL if Amount is 0
However, there is a another way to do the same is by using decode.
Select Decode(Amount, 0, 0, 100/Amount) from Table_Amt.
Result: 0 if amount is 0.
Which one is better? Honestly, I think the decode is much better by returning 0 instead of NULL.