Better than a Spreadsheet

Grouping is great, but it should be just the beginning. How many times you had some aggregates (totals, counts etc), but could not immediately and instantly expand ANY row, to see what records have been actually considered together? In Query Xtractor, result rows of any GROUP BY query are designed to be further navigated this way!

Limited Budget and Infrastructure

Carmen is the Account Manager of a medium-size start-up. “Start-up” means often “work-in-progress” and limited system infrastructure. When Carmen was hired, they made it very clear they cannot afford SAP or Oracle Financials here. Their whole sales system was quickly implemented on MySQL by an intern, long ago. And the user interface didn’t offer many ways to analyze, group and aggregate data.

Carmen was usually getting her accounting reports through the technical administrator. The guy was connecting directly to their database and writing some SQL queries. Carmen was telling him what she was looking for, and the guy was scratching his head to figure out how to write the query. Often, it was not what Carmen was looking for (oh, the beauty of communication and team work…).

This is when they looked at Query Xtractor

Carmen didn’t know SQL at all, but she was familiar with some grouping and summaries from Excel. And most of her data was in a table Invoice, which had one entry for each sale made. Here is how quickly she was able to design one single very powerful query that she still uses daily today for most of her needs.

It looks complex (click for a bigger picture), but this query can be built in just a few seconds by anyone. Step by step:

1. Create a new query from the table Invoice.
2. Check the shape item BillingCountry and change its column name to Country. This will be our group-by field, as we want aggregates per country.
3. Check the shape item InvoiceId and change its column name to Total Invoices. While this always has unique values, a Count on this field will give us the total number of invoices (per country, in this case).
4. Check the shape item CustomerId and change its column name to Total Customers. A Count on this field will give us the total number of customers (per country, in this case).
5. Check and then double click three more times on the shape item Total. Then change the column names to Total Amount, Average Amount, Min Amount and Max Amount. We want different aggregates on this field.
6. Run the query and see how it looks…
7. Total is not formatted as currency, so we may set a Currency format for all Total aggregate fields.
8. From the Group row, select Sum, Average, Minimum and Maximum for the Total columns, Count for InvoiceId and CustomerId. What is left (the field Country) is our lonely internal GROUP BY field.
9. Also, we may start with the country which brings us most revenue, so select Sort Descending for Total Amount.
10. Run again the query and see how it looks…
11. You may also set bottom page Summaries: grand totals (Count) for the number of countries, invoices and customers, grand sum and average for Total, plus min/max overall values.

And …this is it! You made just suggested selections and provided more friendly column names, but you did not have to type one other single character! And you have now all this aggregate information at your finger tips. “SQL”? What is that?

The Power of Drill-Down

But wait, there is more! (Would you like to know more? I do). Who are your actual customers from …France? And from Germany? And from… Now we are talking. And start to drill-down, hmm, the beauty of drill-down…

As I said, any such GROUP BY query (i.e. when Group row is visible in our query builder) creates a specific result, with rows that allow instant drill-down to their expanded aggregates. Just click the plus icon from each row and you see the actual invoices aggregated for a country.

Other columns may be added to these expanded records, that may further allow you to walk on specific relationships.