Aggregating Data and Aggregation Strategies

This weeks training as part of the Free SQL Training for the 70-461 certification course is now available on YouTube. This week we covered Aggregating Data and Aggregation strategies. This training is made available as part of my position at Emergency Reporting.  For more information on sessions, take a look at the Free SQL Training course that we have outlined.

Here is the video.

The first half is presented by Aaron Buma and the second half is me. This is the first public broadcast of 45 weeks for Free SQL Training.

Here is the outline for the session:

  • COUNT(), SUM(), MIN(), MAX(), AVG()
    • —Accounting for NULLs
    • —Grouping on multiple fields
    • —Ordering Results
  • —HAVING – Filtering on Aggregate fields
    • —Filtering on Aggregate and non-aggregate fields
  • OVER Clause
    • With GROUP BY
  • Performance

Here are the slides:

Download this zip file for the slides and sample code used in the training.
70-461 Training –


Free SQL Server Training

As part of my new role as Director of Research and Development at Emergency Reporting, I have been asked to help the development team with training so that they can all take and pass the SQL Queries 70-461 Microsoft certification. Emergency Reporting recognizes the value in having all of developers or engineers well trained in SQL Server programming, since this is such a large part of what we do.

After looking it over and understanding all the topics needed for the 70-461 SQL Queries certification, Aaron Buma and I we have worked out a plan to get everyone on the development team through this training.

Based on the amount of value that I have received from the SQL Server community, I have been able to get approval to share this training with anyone who wants to participate.

Free Training

Since we will be doing all this training, the prep work to train one team or hundreds of people doesn’t change much, it’s still the same amount of work. So we have decided to open up the training to anyone who wants to attend via a webcast using Google On Air Broadcasts.

There are two options to access this training. One is to participate in the live training, and the other option is to follow my YouTube channel.

Schedule / Plan

Over the next 45 weeks we will be teaching one or two topics each week on Thursdays at 9:00am (pacific time) for about 70 to 90 minutes each week.

Each week when we cover a topic, we will be covering in detail as to be ready to address any questions that might be asked as part of the 70-461 exam.

Here is the plan for the next few weeks.

The following topics will be covered week over week until we get through them all.

  • Stored Procedures – Stored Procedure Techniques
  • Table Data Actions
  • Database Schemas and Synonyms
  • SQL Error Messages
  • Error Handling
  • Data Type Usage
  • Special Data Type Options
  • Dynamic SQL
  • Transactions and Locking
  • Isolation Levels and Concurrency
  • Spatial Data Types
  • Spatial Aggregates
  • XML Modes
  • Shredding XML
  • Shredding, attributes, elements and levels
  • Creating Indexes
  • Creating Indexes with Code
  • Index Analysis
  • XML Queries
  • Xquery
  • XML Data Binding
  • The GUID Data Type
  • Constraints
  • Importing and Exporting XML
  • XML Namespaces
  • XML Schemas and Processing
  • After Triggers
  • Other Triggers
  • XML Indexes
  • Views
  • Updating and Maintaining Views

This schedule is subject to change, but that’s our best guess at the plan for now.

About the Instructors

Steve Stedman … That’s me, for more information on me, just visit my blog at I am the Director of Research and Development at Emergency Reporting, I have spoken at many SQL Saturdays, other development conferences and local events. 25 years of SQL Server experience starting in 1990.

Aaron Buma … Research and Development Engineer at Emergency Reporting. About 10 years of SQL Server experience in high availability environments. Visit for more details. Aaron is also known for building and flying RC quadcopters / small UAV’s for aerial photography.

How to participate

Contact either of us through LinkedIn, Youtube, or Google Plus and let us know you want to participate, or just follow me on Twitter or Google Plus to get notified of upcoming sessions.

Just check my Google Plus profile every Thursday at 9:00am (pacific time) or a few minutes early to join in the weekly training.

We will also start using the #SqlClass hash tag on Twitter for notifications of upcoming events and where to find the recorded material.

Reference Material

For everyone attending the classes at Emergency Reporting we will be using the Joes 2 Pros SQL Queries books, however when purchasing them from Amazon, they appear to have been back ordered a bit, and if we are not able to get those, we may choose a different book set.

SSMS Results In A Separate Tab

At the end of my SQL Saturday presentation today on Advanced Common Table Expressions, which is about the 12th time I have presented on CTEs, I was asked a question that I have been asked many times after I present.  I am blogging about this because I have been asked about it so many times. This one one of those hidden gems in SSMS that just makes working in SSMS that much easier.

The question was “I have my results and messages showing up below my T-SQL editor in SQL Server Management studio, how do you get it to show up as 3 tabs, one for the editor, one for results and oen for messages.

The default configuration of SSMS your results and messages windows are shown below the query window as shown below:


This configuration is just fine when you are running at a higher resolution, but when you are running at a lower resolution, like most projectors require, it is easier to see what is going on when the Results and Mesages are displayed on separate tabs at the top level as shown in the next two screen shots.



Based on the default configuration, it is not exactly obvious how to do this.

Here is how you do it.  From the Tools menu in SSMS choose Options. The following dialog will be shown.  Expand the Query Results setting, then SQL Server, then click on the Results to Grid (or Results to Text) depending on your preference.  There are two checkboxes that you should check to get the desired behavior.  These are highlighed in the image below.  “Display results in a separate tab”, and “Switch to results tab after the query executes”.  Click both of these then click the OK button.  Depending your your version of SSMS you may need to restart it for the changes to start working.


From this point forward SSMS will default to showing the results in a separate tab.  For SQL Saturday or other presentations this is a great way to share more of the result set with the audience.

Introducing the Joes2Pros Academy

The new Joes2Pros Academy has just launched. The Academy provides an interactive classroom experience that can be accessed on your own time as you need it. This site has high quality content, quizzes and exams.

Students can interact with other students and instructors to get the best experience out of the Joes2Pros Academy.

Courses offered in the following areas:

  • SQL Server Administration
  • SQL Server Developer
  • SQL Server BI
  • SSRS
  • AlwaysOn
  • C#
  • and more

The Joes2Pros Academy helps you take the journey from average Joe to technology Pro.

As an instructor in the Joes2Pros Academy I have been able to learn a great deal from the other instructors.  

End of June Summary

June was an exciting month for me with Beta 6 of the Database Health Reports releasing at the beginning of the month, and this being CTE Month at to mark the release of my Common Table Expressions book.

CTE Month Summary

CTE Month postings started with the basics on June 1st, and grew throughout the month to include more advanced CTE content.

Since June is CTE month, here are some links to other CTE articles on my blog:

You can also find more CTE examples on the TSQL Wiki.

If you enjoyed these Common Table Expression posting, and want to learn more about Common Table Expressions, please take a look at my book on CTE’s at The book is titled Common Table Expressions – Joes 2 Pros® – A CTE Tutorial on Performance, Stored Procedures, Recursion, Nesting and the use of Multiple CTEs.

Other Posts for June

Allowing Inbound Connections to SQL Server on Windows Server 2012

A three and a half minute video showing how to configure SQL Server 2012 on Windows Server 2012 to allow for inbound connections on the default port of 1433.


  1. Configure SQL Server to use TCP connections.
  2. Allow inbound connections through the firewall.
  3. Test it from another server.