This blog comes from the Vertica Architecture and SQL book by Tom Coffing and David Cook.
Are you ready to learn Vertica SQL from the best technical trainer the world has ever seen? Tom Coffing, AKA Tera-Tom, makes learning fun, exciting, and easy. Learning SQL is one of the best moves you can make in your career because you can query every database with SQL.
Tom became famous as a teacher because he memorized every student's name when he greeted them. Tom has taught over 1,000 classes and never missed a single name. Ask anyone who attended a class taught by Tom Coffing.
Tom has also led a team of developers for almost 20 years to create the Nexus, which queries, migrates, and joins data across all systems. Amazingly, Nexus migrates between all the databases below with the click of the mouse.
• MySQL
• Excel
• Microsoft Access
• Snowflake
• Redshift
• Synapse
• BigQuery
• Teradata
• Oracle
• SQL Server
• DB2
• Hadoop
• MapR
• Greenplum
• Postgres
• Vertica
• Netezza
• SAP HANA
• Yellowbrick
• SQLite
The blog is below this first video, but did you know you can migrate directly to Vertica in a heartbeat with Nexus? This video shows the many features and options for migrating 19 different databases to Vertica with the click of a button. We also show how we can join tables from 19 different systems with Vertica in a single query (federated query). We also show how to search for column names (i.e., customer_number) across all systems in your enterprise and then join them. Finally, you will see that users can work from home to migrate and join data, but they have the option to execute the migrations from a Nexus Server (located on-premises or on any cloud) for high-speed transfers.
The Amazing GROUP BY GROUPING SETS for Advanced Aggregation
Few people know about or have ever seen a GROUP BY GROUPING SETS query, but prepare to be amazed.
Most SQL developers deal a lot with aggregation and understand that you must use a GROUP BY when involving aggregation with normal columns. However, in the query below, we don't do a GROUP BY but instead do a GROUP BY GROUPING SETS.
The GROUP BY GROUPING SETS will, in a sense, combine different GROUP BY statements in a single report.
In the query below, we have three GROUPING SETS:
Product_ID
MTH
YEAR
Check out the answer set and focus on what I will tell you. First, notice that we made $862,404.35 in the year 2000. Now, focus on that we made $418,769.36 in September and $443,634.99 in October, but if you total them up, it equals $862,404.35, which is our yearly total.
Finally, focus on what we made per Product_ID and understand that if you total Product_ID 1000, 2000, and 3000, it equals $862,404.35, which is our yearly total.
GROUP BY GROUPING SETS gives you a single report with multiple ways to view the amounts.
In the next query below, we have five GROUPING SETS:
YEAR
PRODUCT
SALES_PERSON
REGION
QUARTER
First, notice line one in the answer set, which tells us that in 2020, our sum is $25,875,689.91 for the year. Now, focus on that our salespeople sell four products: Boots, Jackets, Jeans, and T-Shirts, and what we made for each product combined in 2020 totals $25,875,689.91.
Next, focus on our four salespeople: Gary Lewis, Helen Smith, Mary Jones, and Will Davis, and what they sold in 2020. Combine their total sales, and the total is $25,875,689.91.
Next, we see what the Regions: North and South sold during 2020, totaling $25,875,689.91.
Finally, we see what we sold in 2020 per quarter, totaling $25,875,689.91.
This example was taken from Nexus in dark mode, where the user chose to put the answer set to the right of the query window (book mode).
It is never too early to teach your children about data. Do what this family does and read them a story of the greatest data scientist ever!