1. Formatting tools
Let's take a look at some helpful tools for displaying summarized data.
2. Before formatting
This query displays the Total Duration in the BikeShare dataset grouped by each weekday name.
SQL default sort order is alphabetical, but users usually want to see this in a logical calendar order. The TotalDuration display is also not ideal as a large number without commas separating. Let's see how we can remedy both of these issues.
3. Sort by logical weekday
4. FORMAT ( value, format [, culture ] )
FORMAT() is a useful SQL function that changes the way a value is displayed according to the input parameters of format and culture, which is an optional parameter. It returns this value as an nvarchar data type so FORMAT() should be used when additional calculations are no longer needed on numeric or date values. This functions' applications are vast and could be explained more extensively, but we will narrow our focus on how the Duration and StartDate can be displayed in our query.
The first column, German date, uses FORMAT(), passes d for date as the format type, and de-de as the CULTURE parameter. The second is a date formatted for the US English culture by passing d as the format type and en-us as the culture. The German Duration column passes n, which stands for numeric, as the format type and again de-de as the culture. For the US English Duration we pass n as the format type and en-us as the culture. The last column shows one way you can utilize custom numeric formats if the predetermined options are not the desired result.
Don't the results look much better and are easier to read?
5. All together now
Let's apply the weekday sort order and FORMAT() function to the non-formatted data from the beginning of the lesson. The FORMAT() function uses a custom type to add commas and decimal places to the TotalDuration, and the CASE statement in the ORDER BY clause allows the weekday values to be sorted logically instead of alphabetically. This is much easier to interpret than it was previously.
6. Your turn!
It's time for you to get some practice adding finishing touches to your data results.