625938_04_02_XR15_Functions
- [Instructor] Besides pulling information out of our database in an organized fashion, queries will also help us out to view a bigger picture of what's going on within the organization. We can use queries to get line values together in new ways to give us more insight than what the raw data can reveal alone. Let's go back into our WideWorldImporters database and expand the Tables folder, and I'm going to look for a table here called Sales.InvoiceLines. I'll right-click on it and choose Select Top 1000 Rows. In the results, we can see that each order line includes data on the quantity for each product, the unit price, as well as the tax rate for each product ordered.
Using these values, we can calculate a total amount due for each order line. To do this, let's go ahead and close this query and we'll start up a new one of our own. We'll start with the SELECT statement. The first column I want to see is the OrderLineID. I'll type in a comma and come down to my next line. For legibility, we can place each column name on its own line by pressing Enter after the comma, then use the Tab key to make sure that they're all aligned. In addition to being casing-sensitive, Transact-SQL ignores additional whitespace in the query syntax, so feel free to organize the text in any way that's easy to read.
The next column is going to be the Quantity, then we'll come down to the next line and type in UnitPrice, and finally the TaxRate column. The last column in the list doesn't get a comment after it, so I'll just press Enter to go down to the next line and we'll type in our FROM clause. The table we want to pull it from is Sales.OrderLines. Let's make sure that the WideWorldImporters database is the one currently selected in this drop-down menu and then press the Execute button.
If you typed in everything correctly, you should get these results down below with the columns of OrderLineID, Quantity, UnitPrice, and TaxRate. In order to add in a new calculation as a fifth column, we'll just add it up here to the SELECT statement. I'll come with after TaxRate and I'll type in a comma, and I'll come down to the next line. This first calculation is just going to multiply the Quantity times the UnitPrice. We also need to name this new column and for that we'll use the AS keyword. I'll type in AS followed by the name that we'd like to appear at the top of the column header and I'll type in ExtendedPrice.
I'll type in a comma and come down to the next line. We can also find the amount of tax due by multiplying the Quantity times the UnitPrice times the TaxRate. Now, because the TaxRate is a rate, we need to actually divide it by 100, so I'm going to type in a parentheses, TaxRate divided by 100, and then the closing parentheses. Once again, we'll type in the AS keyword and the column header name will be TaxDue.
Type in the comma and come down to the next line. And finally we can get the line total by adding all of these values together. We'll type in a parenthesis here and Quantity times UnitPrice, we'll close that parenthesis and we'll add that to the next calculation, so I'll open up a parenthesis again. Quantity times UnitPrice times TaxRate.
This column will be called TotalPrice. Let's go ahead and execute this query to see the results down below and now we have three additional columns of data that are being calculated by the database without having to be stored in any of the data tables. We can see the ExtendedPrice is the multiplied value of the Quantity times the UnitPrice, the TaxDue is here from the TaxRate, based off of the UnitPrice and the Quantity, and the TotalPrice adds both of these values together. SQL Server also has a variety of functions available that can be used to further modify our query results.
One of the functions at our disposal is the FORMAT function and it can be used to convert regular numeric fields, such as the TotalPrice column, into a current formatted number. To do that, we'll go back up to our query syntax and we'll wrap the FORMAT function around this calculation on the last line. I'll come to the beginning, I'll type in FORMAT and an open parenthesis, then I'll come to the very end, right before the keyword AS, I'll type in a comma and then a single quote, capital C, and a closing single quote to determine that we want to return this number as a current data type and then I'll finish that with the closing parenthesis.
Now when I execute this, we should see a formatted value here underneath TotalPrice including the dollar sign and the correct number of decimals for the cents. This formatting is based off the language and location settings used on this computer, so if you're working outside of the United States, you might see slightly different results or variation of the currency display. So, that's just a sample of the types of calculations that can be performed on a query. For simple mathematical operations to more complex programmatic functions, the SQL Query Designer will be able to return values exactly as you need.
For more information on the wealth of functions available within SQL Server, you'll want to return to the SQL Server 2017 technical documentation and go down here to the Reference section, click on T-SQL, and then from the left-hand menu, click on Functions. This'll give you an overview of all the different functions that are available inside of SQL Server. So, with this resource, you'll get access to the information about all of the functions available as well as notes on how to apply them in your queries.
▶️ DTube
▶️ IPFS