Finding the Sigma Gem

If you’ve read any of my other “Raiders of the ‘Lost’ Toad” blogs, then you know that this blog series is all about helping Toad users find those “undiscovered” gems in the product’s graphical interface… gems that, when touched, increase your productivity, and make a difference in the quality of your life.

How Many Will Pass This Short Quiz?

In Toad for Oracle, given the screen capture below, what’s the quickest way to add up ALL the Balance amounts in the screen snap shown? Note that the data grid displays only a small fraction of the table’s 1,000 rows.

If you know already, then CONGRATULATIONS because you’re in an elite group! Less than 5% of all Toad users know how to add up all 1,000 Balance numbers in just three clicks! If you don’t know, read on to find out how you can join the Toad User Elite.

What is Sigma and what does it do?

The short answer to the quiz above is that there’s an elusive gem of an icon in Toad that aggregates numeric data quickly. We’ll call that icon gem “the Sigma”, which looks like this: Σ. If the symbol looks Greek to you, that’s because it is. The Greek letter Σ is the equivalent of the English letter “S”. It’s used frequently in the mathematical and physical sciences to denote useful summing operations. The example below uses the Sigma as a shorthand notation to represent the infinite addition of a set of fractions:

But don’t let the above scare you. Just remember that Sigma means “Sum” or even better, “Calculate”, or “Aggregate”.

Toad for Oracle’s interface has an icon bearing this Sigma symbol and it can be seen in the toolbar above the data grid in Toad’s Schema Browsers and Editors.

Why is this icon important?

Some of you may have found it an arduous task to merely add up some numbers in a Toad data grid. The Sigma icon gem can save you loads of time. Here’s that quiz scenario again: you’ve executed a query in the Editor and finally get the base query results you’ve been looking for. But now you need to quickly add up some numbers in one of your data grid columns. Perhaps you’ve employed some of these options in the past before reading this blog post today…

Yeah, there’s the brute force method: adding up the numbers by hand or via a calculator
(don't even go there!).
You could go back to your query, add a SUM() function and GROUP BY clause and re-execute the query.
(Ugh! Way more than three clicks, involves some typing, and then take cover! You might get possible DBA flame because re-running your query just consumed more unnecessary resources on the database server!)
Yes, you could highlight a section of the numbers in the grid, copy and paste them into Excel and issue a SUM() function.
(Meh. More than three clicks and might be some typing.)
OK, there’s Toad’s pivot grid mode. That’s not a bad option, but it’s still more than three clicks.
(Maybe details on this gem in a future blog post. 😊)
And then, there’s the Sigma. 

How does it work?

That’s a really good question. You’ll see the Sigma icon at the top of most Toad data grids. But you may not have even noticed its subtle presence because it normally appears as “untouchable”, grayed out, or inactive. Here’s some “keys” to unlocking the magic of this gem…

1. To activate Sigma’s power,
   make sure your cell focus is on a numeric data cell (or cells).
   
   
2. What metrics do you want Sigma to produce?
   Summation is the default aggregation mode if you simply press the Sigma button. But you can use the overload drop-down arrow beside the button to select additional aggregation modes. These aggregations will appear in the data grid’s “footer” section below the last displayed row.
   
   
3. Which row/column/region do you want to calculate over?
   1. As long as your highlighted region includes cells with a numeric data type, you’re good.
   2. If your highlighted region spans non-numeric fields, these will be conveniently ignored, and only numeric cells will be “aggregated” over.
   3. Make use of convenient shortcut keys to highlight cells beyond the visible scope.
      For example, starting with a single cell focus, Shift-Ctl-End will highlight all cells in the grid column following the cell you started in.

The Quiz Solution

And that brings us to the answer for today’s quick quiz. What are the three clicks to total up the Balances in those 1,000 rows?

1. Click on the first row’s Balance cell.
2. Shift-Ctl-End to highlight all cells in (only) the Balance column.
3. Press the Sigma. Aggregations will appear in the aggregation footer line below the grid display.

That’s it, just three keys to unlock the power of Sigma. Just remember that the icon becomes active only when numeric data cells are part of your highlighted selection. Most users will want to highlight numbers in a column. Or maybe in a row. Or maybe a contiguous region of cells. Any of these operations will make the Sigma active.

So congratulations! Now you know something that 95% of Toad users don’t know. You're in the Toad User Elite!

Help your colleagues

If you think your colleagues would benefit from Gary's productivity blog, share it now on social media with the buttons located at the top of this blog post. Thanks!

Got questions?

Our Toad family of products are continually evolving and getting better because we listen to you, our customers.
Do you have questions? Please post questions to the Toad for Oracle forum on Toad World®. 

More blogs from Gary

For more real-world stories like this one, and other Toad solution insights, read Gary’s other blog posts.