Dec 19, 2022 1:48:10 PM by Gary Jerep
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.
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.
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.
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.
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…
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?
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!
Database admin/development doesn't have to be hard.
|
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!
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®.
For more real-world stories like this one, and other Toad solution insights, read Gary’s other blog posts.
Tags: Toad for Oracle Aggregation Sigma
Written by Gary Jerep
As a Software Consultant within Quest, Gary Jerep has over 20 years of experience assisting DBAs, Developers and Analysts with relational database needs. Prior experience includes systems design and analysis, operations research, Business Intelligence, and end-user experience monitoring.
We use cookies to improve your experience with our site. By continuing to use this site, you consent to our use of cookies. Learn more.