Getting Started With Oracle Week: Aggregating

I probably should have written this one first

Most of these are exactly the same as in SQL Server. There are a whole bunch of interesting analytic functions, most of which should look pretty familiar to anyone who has spent time querying SQL Server. Most, if not all, can be extended to be window functions, if you need per-group analysis of any kind.

Counting!

All of this works the same as in SQL Server.

Oracle does have something kind of cool if you only need an approximate count of distinct values. Don’t ask me why there isn’t a similar function to get an approximate count of all values. I wasn’t invited to that meeting. This is good for really large data sets where you just need a rough of idea of the values you’re working with.

Sums and Averages

Fun fact: under the covers, AVG is just a SUM and a COUNT anyway.

The Max for the Minimum

You also have your MIN and MAX functions, along with the HAVING clause, to filter aggregates.

Something not boring

The LISTAGG function is something I’d absolutely love to have something like in SQL Server. It takes column values and gives you a list per row, delimited by the character of your choice. It’s pretty sweet, and the syntax is a lot easier to bang out than all the XML mumbo jumbo in SQL Server.

And the puppies is staying, yo!

And the puppies is staying, yo!

For reference, to do something similar in SQL Server, you need to do this:

Good luck remembering that!

SQL is a portable skill

Once you have the basics nailed down, and good fundamentals, working with other platforms becomes less painful. In some cases, going back is the hardest part! My knowledge of Oracle is still very entry level, but it gets easier and easier to navigate things as I go along. I figure if I keep this up, someday I’ll be blogging from my very own space station.

Thanks for reading!

Previous Post
Getting Started With Oracle Week: Joins
Next Post
Getting Started With Oracle Week: NULLs and NULL handling

11 Comments. Leave new

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}