Make delicious recipes!


Aggregate functions:

  • avg(...), sum(...), min(...), max(...)

  • count(*)

  • count(...), count(distinct ...), count(all...)

Some other useful functions:

  1. initcap - function that capitalizes first character and lowercases remaining.

  2. greatest - returns greatest value among its args.

Example: greatest (2, 5, 11, 3) returns 11.

greatest (‘2’, ‘5’, ‘11’, ‘3’) returns ‘5’ (compares lexicographically for strings)

  1. least - returns least value among its args.

Decode Function

It is like a case statement.

// Example 1: (Converts column region from ‘N’ to North, ‘S’ to South and so on)

// Example 2: (book_type and pub_name are columns in below query)

We could have also achieved the above by a double group-by function as

group by pub_name, book_type

This would avoid the use of decode but then the resulting table would not have all the data related to pub_name in one row (it would be spread across multiple rows).

While this may be ok if the result of this query has to be processed in Java program but the same may not be good if the result of this query has to be feeded into another query.

A problem with above approach (as compared to double group by) is that it can work only if the book_type values are known beforehand and their count is not much. If that were not so, then it would be a pain to write all the known book-types into one query and SQL also might have some limitation on that (like CASE statement has).

Nevertheless, the above is a great example to see usefulness of decode for smaller range columns. Also, if we need to break up a column into ranges, then decode is very powerful, as shown in next example:

// Example 3: Below query selects SAL column into ranges.

// greatest(SAL,3001) will select SAL only if SAL > 3001

// least (SAL,9999) will select SAL only if SAL < 9999

// And if the two are equal, then we will get 1 else 0

// Hence column named “$3001-9999” in the output will return

// the number of salaries between 3001 and 9999

Like us on Facebook to remain in touch
with the latest in technology and tutorials!

Got a thought to share or found a
bug in the code?
We'd love to hear from you:

Email: (Your email is not shared with anybody)

Facebook comments:

Site Owner: Sachin Goyal