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

  • We really need LISTAGG. All the other kids on the block have it (Oracle, Postgres STRING_AGG, MySQL GROUP_CONCAT).

    • Erik Darling
      June 8, 2016 8:24 am

      MySQL doesn’t even have CTEs and they have this. Postgres just got parallelism and they’ve had this. Oracle… uh… Whatever. Read this in call caps for effect.

      The amount of time and effort that is expended on forums answering questions about this is astronomical. And just when you think it’s all figured out, you get “& #x0D;” in your results.

      • MySQL is more interested in helping you be terrible than helping you be awesome.

      • Heck, MySQL world is pretty good these days: few solid working forks, a lot of tested cluster/replication solutions that actually work. InnoDB/XtraDB engine is nicely scaling with hardware while people are getting other engines like Rocks or Toku to work with it successfully.

        Programming-wise? Can’t say anything specific, it’s just I’m more fond of database languages derrived from ADA. And T-SQL is only one that stands out in that flock.

  • Hi

    Just wondering why you’re multiplying the AVG_SALARY fields by .100? Wouldn’t that give you 1/10th of the right figure? Or is it a typo for multiplying by 1.00 to get an average to two decimal places?

    Not-an-oracle-guy

  • APPROX_COUNT_DISTINCT is a fascinating function (to me at least).

    It uses the HyperLogLog function – cool name for a function!

    https://justdaveinfo.wordpress.com/2016/06/08/oracle-approx_count_distinct-function/

  • Thank you for Oracle Week! I am really excited about this. I’ve been a SQL Server DBA for over 10 years now and I’m having to learn Oracle now. This post is just what I need!

    • Erik Darling
      June 10, 2016 10:44 am

      Glad you’ve been enjoying it. I really enjoyed writing it. Good luck with the Oracle-ing.

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