Home > Articles > Data > SQL Server

  • Print
  • + Share This
Like this article? We recommend

Other Aggregate Functions

The other aggregate functions supported are CHECKSUM, CHECKSUM_AGG, and BINARY_CHECKSUM. All of these functions are used for examining the checksum of columns within a table and determining whether values in a table have changed. I have found very limited use for these functions and won't discuss them in this article. Feel free to consult the SQL Server online documentation for detailed discussion of these functions.

Using Aggregate Functions with GROUP BY

Of course, the discussion of aggregate functions would be lame without their most common use—within the GROUP BY statement. Occasionally, you might see the variations of GROUP BY with ROLLUP and CUBE.

Aggregate functions such as SUM and AVG are used within GROUP BY 90% of the time you touch them. GROUP BY simply lets you examine aggregate values grouped per individual value. For instance, to determine average sales price for various categories of titles, I can use the following query:

SELECT 
        type,
        AVG(price) AS avg_sales_price
FROM titles
GROUP BY type

Results:

type

avg_sales_price

business

13.73

mod_cook

11.49

popular_comp

21.475

psychology

13.504

trad_cook

15.9633

UNDECIDED

NULL


Now, if I wanted to limit the output only to the books with the average price of $15 or greater, I could add the HAVING clause, as follows:

SELECT 
        type,
        AVG(price) AS average_sales_price
FROM titles
GROUP BY type
HAVING AVG(price) > 15

Results:

type

avg_sales_price

Popular_comp

21.475

trad_cook

15.9633


The ROLLUP and CUBE operators provide an option of summarizing the data (don't worry; I'm not talking about OLAP here). ROLLUP and CUBE are useful when you want to generate a quick report inside the Query Analyzer and don't have more-sophisticated reporting tools.

ROLLUP simply provides a "grand total" at the end of a query containing an aggregate function, as in the following:

SELECT 
        title_id,
        DATEPART(YEAR, ord_date) AS year_sold,
        SUM(qty) AS total_qty
FROM sales
GROUP BY title_id, DATEPART(YEAR, ord_date) WITH ROLLUP

Results:

title_id

year_sold

total_qty

BU1032

1994

15

BU1032

NULL

15

BU1111

1993

25

BU1111

NULL

25

BU2075

1993

35

BU2075

2002

55

BU2075

NULL

90

BU7832

1993

15

BU7832

NULL

15

MC2222

1993

10

MC2222

NULL

10

MC3021

1994

40

MC3021

NULL

40

PC1035

1993

30

PC1035

NULL

30

PC8888

1993

50

PC8888

NULL

50

PS1372

1993

20

PS1372

NULL

20

PS2091

1994

108

PS2091

NULL

108

PS2106

1993

25

PS2106

NULL

25

PS3333

1993

15

PS3333

NULL

15

PS7777

1993

25

PS7777

NULL

25

TC3218

1992

40

TC3218

NULL

40

TC4203

1992

20

TC4203

NULL

20

TC7777

1992

20

TC7777

NULL

20

NULL

NULL

548


Notice the last row—it contains the grand total of all quantities sold. Also notice that because we grouped the output by the year titles were sold as well as by title identifiers, we have the "running" totals along the way for each title.

The CUBE operator can be thought of as a two-dimensional representation of a multidimensional data cube. CUBE is similar to ROLLUP, but it produces the grand totals for each item involved in GROUP BY in addition to the running totals. Check out what happens to the same query as above when we change ROLLUP with CUBE: We get the total quantity of books sold in each year:

SELECT 
       title_id,
       DATEPART(YEAR, ord_date) AS year_sold,
       SUM(qty) AS total_qty
FROM sales
GROUP BY title_id, DATEPART(YEAR, ord_date) WITH CUBE

Results:

title_id

year_sold

total_qty

BU1032

1994

15

BU1032

NULL

15

BU1111

1993

25

BU1111

NULL

25

BU2075

1993

35

BU2075

2002

55

BU2075

NULL

90

BU7832

1993

15

BU7832

NULL

15

MC2222

1993

10

MC2222

NULL

10

MC3021

1994

40

MC3021

NULL

40

PC1035

1993

30

PC1035

NULL

30

PC8888

1993

50

PC8888

NULL

50

PS1372

1993

20

PS1372

NULL

20

PS2091

1994

108

PS2091

NULL

108

PS2106

1993

25

PS2106

NULL

25

PS3333

1993

15

PS3333

NULL

15

PS7777

1993

25

PS7777

NULL

25

TC3218

1992

40

TC3218

NULL

40

TC4203

1992

20

TC4203

NULL

20

TC7777

1992

20

TC7777

NULL

20

NULL

NULL

548

NULL

1992

80

NULL

1993

250

NULL

1994

163

NULL

2002

55


  • + Share This
  • 🔖 Save To Your Account