Sunday, October 3, 2010

More SQL pieces

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.