Using T-SQL Functions and Summarizing Results Analyzing and Summarizing Data in SQL Server

  • John Deardurff

Your browser needs to be JavaScript capable to view this video

Try reloading this page, or reviewing your browser settings

You're watching a preview of subscription content. Log in to check access

Enhance your SELECT statements by invoking a vast library of built-in features and capabilities that SQL Server exposes in the form of functions. This video shows how these functions enable data type conversions null detections date and number formatting and much more. Also available are functions helping you to analyze data in the database itself. These functions enable you to summarize data to count occurrences compute averages and generate statistics.

The video begins with an introduction to basic functions to work with character data and NULL values. From there you’ll move on to functions dealing with conditional structures NULL values and formatting. You’ll also cover aggregate functions to average count and summarize data. Finally this video discusses how to group result sets by a field using the GROUP BY clause and how to filter those results after they have been grouped – using the HAVING clause.

Functions are the doorway to an ever-increasing number of powerful database engine features and capabilities. There is so much more that you can do in SQL other than simply SELECT data. Functions enable you to take full advantage of the power that’s available to you in Microsoft’s flagship database engine. This video is the second in a series and leaves you well positioned to step into the next video covering how to select data from multiple tables.

What You Will Learn

  • Sort data into groups and count the records in each group

  • Compute max, min, and mean values for records in a group

  • Format dates and numbers for display to end users

  • Filter to eliminate unwanted groupings from your result set

  • Search, concatenate, and manipulate strings

  • Detect NULL values and replace them with real values

Who This Video Is For

Developers business analysts and others whose career will benefit from knowledge of SQL for accessing Microsoft SQL Server databases and the data they contain. The video is for anyone working in a SQL Server environment and wanting to derive business value from their organization’s data by analyzing that data creating reports and even generating visualizations.

About The Author

John Deardurff

John Deardurff has been a Microsoft Certified Trainer for close to 20 years with experience teaching Microsoft SQL Server Exchange Server and Windows Server certification courses. He has also been a database and networking consultant for the majority of that time and currently holds over 48 Microsoft certifications. John is an MCT Regional Lead for the Eastern United States and a Data Platform MVP.

 

About this video

Author(s)
John Deardurff
DOI
https://doi.org/10.1007/978-1-4842-4548-4
Online ISBN
978-1-4842-4548-4
Total duration
48 min
Publisher
Apress
Copyright information
© John Deardurff 2019

Video Transcript

Welcome to the T-SQL querying video series on working with functions and grouping. My name is John Deardurff. I’ve been a Microsoft Certified Trainer for 20 years. I’m a former MVP on data platform. And I have earned my MBA on IT management.

After this introduction, we will begin looking at functions in a SELECT statement. Then we will learn how to write basic string functions or work with functions that deal with character fields. Then we’ll look at date and time functions, functions that will allow you to work with conditions, a newer function that allows you to FORMAT your result set, and then using functions that work with NULL values.

Next, we’ll work with aggregate functions, or basically, using functions that will summarize or average a series of values to return a single value. We’ll also look at how aggregate functions work with NULL values, the processing order of a SELECT statement, grouping our results, and then filtering out those group results. We’ll finish up with a wrap up.

The prerequisites for this video series is a knowledge of writing a T-SQL statement or if you watched my previous video on T-SQL Querying: Writing a SELECT Statement. Also to practice, you will need a copy of SQL Server and the Adventure Works database. To get a copy of SQL Server and the Adventure Works database, here’s a link at docs.microsoft.com where you can download a copy of SQL Server, and then you could download the Adventure Works database. So we hope you enjoyed this video series.