This page has 0 threads | Add post

Special considerations for aggregation statistics

Written by Andy Brown

In this tutorial

Ways to count rows
Converting data types when averaging
Dealing with nulls

This part of the tutorial on grouping in SQL looks at various special cases of aggregation, beginning with counting rows.

Ways to count rows

You can count rows in 3 different ways:

Method	What it will return
COUNT(*)	The number of rows
COUNT(ColumnName)	Number of rows having non-null column values.
COUNT(DISTINCT ColumnName)	The number of unique values for a column.

Here is an example of each type of syntax:

SELECT COUNT(*)

AS 'Number films in total' FROM tblFilm

SELECT COUNT(FilmCertificateId)

AS 'Number films with certificate id' FROM tblFilm

SELECT COUNT(DISTINCT FilmCertificateId)

AS 'Number unique certificate ids' FROM tblFilm

Here's what this returns for the Wise Owl movies database:

There are 261 films in the database, but 101 of these have a null value for the certificate id. There are only 6 distinct certificate values: U, PG, 12, 12A, 15 and 18.

Converting data types when averaging

Averaging integer fields can be a pain! Suppose that you want to find out the average number of Oscars won for our database. You might think this would do it:

SELECT

avg(FilmOscarWins) AS 'Average Oscars won'

FROM

tblFilm

However, it doesn't. Because the Oscars column is an integer, the data type returned is also an integer - and approximates to zero:

The query returns 0, even though the true answer must be more than this.

The solution is to convert the column being averaged into a non-integer data type before you process it:

SELECT

avg(CAST(FilmOscarWins AS float)) AS 'Average Oscars won'

FROM

tblFilm

However, it's a good idea to then display the result to a smaller number of decimal places:

SELECT

CAST(avg(CAST(FilmOscarWins AS float)) AS decimal(5,2))

AS 'Average Oscars won'

FROM

tblFilm

The before-and-answer results for the above two queries are as follows:


Before rounding	Rounded to 2 decimal places

Dealing with nulls

When you sum a set of values, any nulls will be ignored. For example, here are the 3 films in our database whose names end with A:

One of the films (Kagemusha) has a null value for its budget.

The following query would return the value 73,000,000, the sum of 60,000,000 and 13,000,000:

-- total budget for films ending with "A"

SELECT

SUM(FilmBudgetDollars)

FROM

tblFilm

WHERE

FilmName like '%a'

If you look at the results, there's no sign that you may have missed out values:

The total budget returned by this query.

However, the Messages tab tells the full story:

The waning as to what has happened.

Note that Micrsooft Access would return a null value from this query - SQL behaves differently.

When averaging values (some of which may be null), your best bet is to strip out nulls first (see a separate blog for more on ways to deal with nulls). Consider this query:

SELECT

-- average budget including Kagemusha

AVG(IsNull(FilmBudgetDollars,0)) AS 'Average budget 1',

-- average budget excluding Kagemusha

AVG(FilmBudgetDollars) AS 'Average budget 2'

FROM

tblFilm

WHERE

FilmName like '%a'

This would return two different numbers:

Which is correct? It depends whether you feel that the film Kagemusha should count towards the statistics.

Previous
tutorial

Joins

Previous
part

Using criteria in a GROUP BY query to restrict results

Top of page

Next
part

Varying Grouping Results: Rollup, Cube and Compute

Next
tutorial

Views

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on SQL

Grouping data

Special considerations for aggregation statistics

Ways to count rows

Converting data types when averaging

Dealing with nulls

Head office

London

Manchester