Counts and Totals

• Examining Totals with Counts

        SELECT COUNT(*) FROM Incidents
  

  The above sql statments returns the total number of rows in the Incidents table.

• Count with DISTINCT

        SELECT COUNT(DISTINCT Country) AS Countries
        FROM Incidents
  

  The above sql statements returns the count for the number of unique country value from the Country field in Incidents table.

• Count Aggregations

  • GROUP BY can be used with COUNT() in the same way as the other aggregation functions such as AVG(), MIN(), MAX()
  • Use the ORDER BY command to sort the vaues
    • ASC will return the smallest values first (default)
    • DESC will return the largest values first

• COUNT With GROUP BY

  Count the rows, subtotaled by Country

        SELECT 
            COUNT(*) AS TotalRowsByCountry, Country
        FROM Incidents
        GROUP BY Country
  

• COUNT With GROUP BY and ORDER BY

  Count the rows, subtotaled by Country

        SELECT 
            COUNT(*) AS TotalRowsByCountry, Country
        FROM Incidents
        GROUP BY Country
        ORDER BY Country ASC
  

• Column totals with SUM

  • SUM() provides numeric total of the values in a column
  • It follows the same pattern as other aggregations
  • Combine it with GROUP BY to get subtotals based on columns specified

• Adding Column Values

  Calculate the value subtotaled by Country

        SELECT
            SUM(DurationSeconds) AS TotalDuration, Country
        FROM Incidents
        GROUP BY Country
  

---

Math with Dates

• DATEPART

  • DATEPART is used to determine what part of the date you want to calculate. Some of the common abbreviations are:
    • DD for Day
    • MM for Month
    • YY for Year
    • HH for Hour

• Common data functions in T-SQL are:

  • DATEADD(): Add or subtract datetime values
    • Always returns a date
  • DATEDIFF(): Obtain the difference between two datetime values
    • Always returns a number

• DATEADD

  To Add or Subtract a value to get a new date use DATEADD()
  Syntax: DATEADD(DATEPART, number, date)

  • DATEPART: Unit of measurement (DD, MM, etc.)
  • number: An integer value to add
  • date: A datetime value

• Date math with DATEADD

  What date is 30 days from June 21, 2020?

        SELECT DATEADD(DD, 30, '2020-06-21')
  

  The above sql returns the date ‘2020-07-21’

  What date is 30 days before June 21, 2020?

        SELECT DATEADD(DD, -30, '2020-06-21')
  

  The above sql returns the date ‘2020-05-22’

• DATEDIFF

  Returns a date after a number has been added or subtracted to a date
  Syntax: DATEDIFF(datepart, startdate, enddate)

  • datepart: Unit of measurement (DD,MM,etc.)
  • startdate: The starting date value
  • enddate: An ending datetime value

        SELECT
            DATEDIFF(DD, '2020-05-22', '2020-06-21') AS Difference1,
            DATEDIFF(DD, '2020-07-21', '2020-06-21') AS Difference2,
  

  The above sql statement referes the difference in day between stardate and enddate provided.

• Rouding numbers

  Syntax: ROUND(number, length [, function])

        SELECT
            DurationSeconds,
            ROUND(DurationSeconds, 0) AS RoundToZero,
            ROUND(DurationSeconds, 1) AS RoundToOne
        FROM Incidents
  

• Rounding on the left side of the decimal

        SELECT
            DurationSeconds,
            ROUND(DurationSeconds, -1) AS RoundToTen,
            ROUND(DurationSeconds, -2) AS RoundToHundred
        FROM Incidents
  

• Truncating with ROUND()

  The ROUND() function can be used to truncate values when you specify thethird argument
  Syntax: ROUND(number, length, [, function])

  • Set the third value to anon-zero number

        SELECT
            Profit, 
            ROUND(DurationSeconds,0) AS RoundingtoWhole,
            ROUND(DurationSeconds, 0,1) AS Truncating
        FROM Incidents
  

---

Math Functions

• Absolute Value

  Syntax: ABS(number)
  - User ABS() to return non-negative values

        SELECT ABS(-2.77), ABS(3), ABS(-2)
  

        SELECT
            DurationSeconds,
            ABS(DurationSeconds) AS AbsSeconds
        FROM Incidents
  

• Squares and Square Roots

        SELECT
            SQRT(9) AS Sqrt,
            SQUARE(9) AS Square
  

• Logs

  • LOG() returns the natural logarithm
  • Optionally, you can set the base, which if not set is 2.718281828

  Syntax: LOG(number, [, BASE])

        SELECT
            DurationSeconds,
            LOG(DurationSeconds, 10) AS LogSeconds
        FROM Incidents
  

• Log of 0

  You cannot take the log of 0 as it will give you an error

        SELECT LOG(0,10)