Basic Aggregate Functions

• Key Aggregation Functions

  • Counts
    • COUNT()
    • COUNT_BIG()
    • COUNT(DISTINCT)
  • Other Aggregates
    • SUM()
    • MIN()
    • MAX()

• What counts with COUNT()

  • Number of Rows
    • COUNT(*)
    • COUNT(1)
    • COUNT(1/0)
  • Non-NULL Values
    • COUNT(d.YR)
    • COUNT(NULLIF(d.YR, 1990))

• Distinct Counts

        SELECT
            COUNT(DISTINCT c.CalendarYear) AS Years,
            COUNT(DISTINCT NULLIF(c.CalendarYear, 2010)) AS Y2
        FROM dbo.Calendar c;
  

• Filtering aggregates with CASE

        SELECT
            MAX(CASE WHEN ir.IncidentTypeId = 1
                    THEN ir.IncidentDate
                    ELSE NULL
                END) AS I1,
            MAX(CASE WHEN ir.IncidentTypeID = 2
                    THEN ir.IncidentDate
                    ELSE NULL
                END) AS I2,
        FROM dbo.IncidentRollup ir; 
  

---

• Statistical Aggregate Functions

  • AVG() : Mean
  • STDEV(): Standard Deviation
  • STDEVP(): Population Stadard Deviation
  • VAR(): Variance
  • VARP(): Population Variance

• What about median?

        SELECT
            TOP(1) PERCENTILE_CONT(0.5)
                WITHIN GROUP (ORDER BY l.SomeVal DESC)
                OVER () AS MedianIncidents
        FROM dbo.LargeTable l;
  

• The cost of median

  	Median	Mean
  Est. Cost	95.7%	4.3%
  Duration	68.5s	0.37s
  CPU	68.5s	8.1s
  Reads	72,560,946	39,468
  Writes	87,982	0

---

Downsampling and Upsampling Data

• Data in nature

        SELECT 
            SomeDate
        FROM dbo.SomeTable
  

  

• Downsampling Data

        SELECT
            CAST(SomeDate AS DATE) AS SomeDate
        FROM dbo.SomeTable
  

  

• Further Downsampling

        SELECT
            DATEADD(HOUR, DATEDIFF(HOUR,0,SomeDate), 0) AS SomeDate
        FROM dbo.SomeTable
  

  DATEDIFF(HOUR,0,'2019-08-11 06:21:16') = 1,048,470
DATEADD(HOUR< 1048748,0) = 2019-08-11 06:00:00

  

• What about upsampling?

  Downsampling	Upsampling
  Aggregate data	Disaggregate data
  Can usually sum or coutn results	Need an allocation rule
  Provides a higher-level picture of the data	Provides artifical granularity
  Acceptable for most purposes	Acceptable for data generation, calculated averages

---

Grouping by ROLLUP, CUBE, and GROUPING SETS

• Hierarchial rollups with ROLLUP

        SELECT
            t.Month,
            t.Day,
            SUM(t.Events) AS Events
        FROM TABLE
        GROUP BY
            t.Month,
            t.Day
        WITH ROLLUP
        ORDER BY
            t.Month,
            t.Day;
  

• Cartesian aggregation with CUBE

        SELECT
            t.IncidentType,
            t.Office,
            SUM(t.Events) AS Events
        FROM TABLE
        GROUP BY
            t.IncidentType,
            t.Office
        WITH ROLLUP
        ORDER BY
            t.IncidentType,
            t.Office;
  

• Define grouping sets with GROUPING SETS

        SELECT
            t.IncidenType,
            t.Office,
            SUM(t.Events) AS Events
        FROM TABLE
        GROUP BY GROUPING SETS
        (
            (t.IncidentType, t.Office),
            ()
        )
        ORDER BY
            t.IncidentType,
            t.Office;