Product articles SQL Prompt SQL Code Completion and IntelliSense
SQL Prompt Hidden Gem: Auto-fill the…

17 November 2017

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.

17 November 2017

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

Writing GROUP BY clauses is a very common, but tedious, activity for a SQL programmer. SQL Prompt will fill it in for you, with all non-aggregated columns returned by the query.

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 the GROUP BY clause with the following column expressions:

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

Share this post.

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

You may also like

  • Article

    Quick SQL Prompt tip - restore your tabs, even if you closed them by mistake

    It’s sometimes too easy to close SQL Server Management Studio (SSMS) without saving your work. It’s also easy to get rid of tabs and scripts that you later wish you had back. SQL Prompt can help with both these problems. Take a look at the Options for SQL Prompt and you’ll find a set of

  • Article

    Using multiple custom SQL formatting styles in SQL Prompt

    Louis Davidson shows how to use SQL Prompt formatting styles to create and maintain multiple code styles, each for a dedicated purpose, and to switch between them and apply a new style to existing code, with ease.

  • Article

    SQL Prompt Hidden Gems: The SSMS Results Pane

    Every time you need to reuse the query results from SSMS, for example to populate another table, or to search for matching rows in another table, it will inevitably mean a lot of manual tweaking to the get the results into the right format. Louis Davidson uncovers three hidden Prompt gems (Open in Excel, Script as INSERT, Copy as IN clause) that can remove all this pain.

  • Article

    Enhancing your database development using coding standards

    Development and IT departments face increasing demands to deliver changes faster, often with increasing complexity and less tolerance for downtime, and without a corresponding growth in team size to handle the extra work. Standardizing database development practices by establishing coding standards can help achieve this by removing blockers to understanding code, easing the implementation of

  • Forums

    SQL Prompt Forum

    Write, format, and refactor SQL effortlessly