17 November 2017
17 November 2017

SQL Prompt Hidden Gem: Auto-fill the GROUP BY clause

Guest post

This is a guest post from Louis Davidson. Louis is a senior data architect for the Christian Broadcasting Network in Virginia Beach. A highly experienced Microsoft SQL Server MVP, he speaks at most PASS conferences and a large number of SQL Saturday events.

He regularly contributes articles to both his own blog and Simple Talk, and has written a book on database design for each of the last five major releases of Microsoft SQL Server.

Have you seen this new feature that auto-fills the GROUP BY with non-aggregated columns!” exclaimed my co-worker, soon after she had installed a new version of SSMS. I hadn’t, but I was intrigued. I must have written thousands of aggregate queries in my career, so it sounded like it could save me some time.

I searched around in SSMS, on the Virtual Machine I use for testing new versions of SQL Server, and it wasn’t there. Ah. It turned out that my testing VM didn’t have SQL Prompt installed, and this wasn’t a new SSMS feature, after all, but rather one of those ‘hidden’ SQL Prompt enhancements.

Filling in the GROUP BY manually

Let’s say I need to write a query on the WideWorldImporters database, to get the sum of items that have been ordered, grouped by postal code and the year when the customer’s account was opened.

In typical fashion, I start by filling in the tables and their join conditions. I type “or” to find the tables relating to orders, without even having to know that they live in the Sales schema.

Figure 1

I expand the SELECT list to return, initially, just the base rows that I need to work with. I apply my preferred Prompt formatting style, and the query looks something like this:

The query returns a few hundred thousand rows, a small sample of which look like this:

I want to see total order quantity, by customer delivery postcode, and the year the customer opened their account, so the final SELECT list looks like this:

That just laves the GROUP BY clause and typically, I would just copy and paste the non-aggregated columns in the SELECT clause, and replace SELECT with GROUP BY:

Finally, I just get rid of the aliases to give a final query like this:

All in all, it is a tedious process.

Auto-filling the GROUP BY with SQL Prompt

As with all clauses of a SQL statement, SQL Prompt offers assistance in filling out the GROUP BY; I just hadn’t noticed it before! Just hit the space bar after the clause you type GROUP BY, shown in Figure 2.

Figure 2

Choose All non-aggregated columns, and it will fill in for the following column expressions into the GROUP BY clause:

Alternatively, as you can also see in Figure 2, you can pick the non-aggregated columns individually. If you want to add additional groupings, such as by the month the customer opened the account, simply add MONTH(Customers.AccountOpenedDate) AS AccountOpenMonth to the SELECT clause, delete the existing GROUP BY clause, and auto-refill the column expressions. The GROUP BY clause will now look like this:

In some cases, you may have values in your SELECT clause that are in an aggregate function, but do not need to be in the GROUP BY clause. For example, say we change the SELECT query to include two new columns, highlighted in bold text in the following code. One is a literal value, and the other performs a calculation on a column that is already in the existing GROUP BY:

This query will execute as is, because we don’t need to group by on a literal, nor do we need to group on an expression that only uses columns or expressions already in the GROUP BY.

However, if we delete the existing GROUP BY clause, and have SQL Prompt auto-fill it, then it will look slightly different:

This is a rare case, and will almost certainly not cause any difference to the execution of the query, but be aware of it.

That’s our query finished, but if you want to order the output, try typing ORDER BY and see what comes up!

Summary

Once you install SQL Prompt, you become aware immediately of how it enhances the native IntelliSense features of SSMS, but this can blind you to a lot of other features, hidden in various parts of the tool. Often, I only stumble across them by accident, or when alerted by a colleague. This is just another gem of a feature that I found myself using frequently once I realize it existed!

Writing GROUP BY clauses is a very common, but tedious, activity for a SQL programmer. Replace that tedium with software, I always say.

Tools in this post

SQL Prompt

Write, format, and refactor SQL effortlessly in SQL Server Management Studio and Visual Studio.

Find out more

Guest post

This is a guest post from Louis Davidson. Louis is a senior data architect for the Christian Broadcasting Network in Virginia Beach. A highly experienced Microsoft SQL Server MVP, he speaks at most PASS conferences and a large number of SQL Saturday events.

He regularly contributes articles to both his own blog and Simple Talk, and has written a book on database design for each of the last five major releases of Microsoft SQL Server.

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

Related posts

Also in Hub

Finding code smells using SQL Prompt: procedures that lack schema-qualification

SQL Prompt implements a static code analysis rule, PE001, which will check code automatically, during development and testing work, for occurrences of a stored procedure being called, via the EXECUTE ...

Also in Product learning

How SQL Monitor Monitors Azure-based SQL Server Failover Cluster Instances

If you're working with SQL Server, one of the most appealing features of cloud-based computing services, such as Azure and AWS, is that they make it so much easier to design systems that are both high...

Also in SQL Prompt

Why your Development team needs SQL Prompt

Alongside specialists, an IT department needs people who are broadly skilled and adaptable, who can pivot quickly to new projects, slot easily into new teams, and be productive quickly.

SQL Prompt is...

Also about Intellisense

The SQL Prompt Functionality Finder

You've got SQL Prompt, but are you aware of all the things it can do and how to get Prompt to do it? In this article, Phil Factor provides a handy table to make it obvious.

As soon as you open SQL Pr...

Also about SQL Prompt

How you can improve your SQL with code analysis in SQL Prompt

What is code analysis?

Code analysis is a formal automated process of scanning a piece of software code and deducing potential problems, issues and faults that may not be apparent to programmers at f...