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

You may also like

  • Article

    Finding code smells using SQL Prompt: Asterisk in SELECT list

    Using SELECT * FROM within IF EXISTS statements are fine, but in other contexts it causes several problems. For example, INSERT INTO…SELECT * FROM statement assumes the columns are in a particular order, and that order may not last. Also, selecting all the columns in a table when you don’t need them all can cause

  • Article

    How to record T-SQL execution times using a SQL Prompt snippet

    A SQL Prompt snippet is a pre-defined block of code, which we can insert at the current cursor position, or around currently-selected SQL text, in an SSMS query pane. DBAs use them to store all their most commonly-used checks and routines. It’s often useful to have your favorite troubleshooting scripts available to you within a

  • Article

    How to implement a T-SQL code formatting standard using SQL Prompt

    To what extent should a team attempt to impose a uniform SQL coding format? It can sometimes seem like a futile task. All programmers have a preferred formatting style, to which they profess an unwavering attachment. Check the code of ten programmers, though, and you will likely discover at least 15 different formats, since few

  • Article

    SQL Prompt code analysis: avoiding the old-style TOP clause

    The syntax of the TOP clause insists on a bracketed, or parenthesized, expression as its first argument, so that TOP 34 should, strictly speaking be TOP (34), although if you are just supplying a numeric constant, such as 34, as an argument then either style will work. Early incarnations of TOP could use only a

  • Article

    Quick SQL Prompt tip – using the CDB snippet

    SQL Prompt includes a number of snippets by default that can help you quickly write T-SQL code. These are templates of code that users use regularly. One of the more popular snippets is the ‘cdb’ snippet that helps with quickly creating a new database for development work. This can also be used to ensure production

  • Forums

    SQL Prompt Forum

    Write, format, and refactor SQL effortlessly