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.

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.

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

Find out more

You may also like

  • Article

    Never Create Columns with ANSI_PADDING set to OFF

    There is no good reason for having ANSI_PADDING set to OFF when you create tables in SQL Server. It was provided purely for legacy databases that had code that assumed the old CHAR behavior for dealing with padding, and its use has now been deprecated.

  • Community event

    PASSMN (Minnesota SQL Server User Group)

    The Minnesota chapter of the Professional Association for SQL Server (PASS). PASSMN is a forum to exchange knowledge and ideas to better understand, develop, implement and support existing and future SQL Server technologies. Our technical sessions provide a professional, fun and educational environment for all levels of expertise. Redgate are sponsors of their February meeting

  • 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

    Misuse of the scalar user-defined function as a constant (PE017)

    The incorrect use of a scalar UDF as a global database constant is a major performance problem and should be investigated whenever SQL Prompt spots this in any production code. Unless you need to use these global constants in computed columns or constraints, it is generally safer and more convenient to store the value in an inline table-valued function, or to use a view.

  • Forums

    SQL Prompt Forum

    Write, format, and refactor SQL effortlessly