Grouping and Windowing - Part 1

With this blog post, I am going to pay attention to the data analysis function within SQL Server and talk about Windowing and Grouping Functions. A data analysis function is when a function is used with a set of rows and in return, a single value is produced. The SUM() function is an example of a data analysis function.

Using this table, I can show you some examples

ID    Column1      Number
1      DOGS         1
2      CAT            10
3      FISH           9
4      BIRD          NULL
5      CAT           4
6      BIRD          8

Single Grouping Sets Examples

SELECT COUNT(*) NumberOfPets, COUNT(Number)  NumberOfPetsDistincts
FROM [Pets]

Returns

NumberOfPets NumberOfPetsDistincts
6                 5

This is because there is not a GROUP BY statement

SELECT [Column1],COUNT(*) NumberOfPets, COUNT( DISTINCT Number)  NumberOfPetsDistincts
FROM [Pets]
GROUP BY Column1

Returns

The reason the there are less BIRD's in the NumberOfPetsDistincts columns is because  COUNT( DISTINCT Number) ignores NULLs

Part 2 can be found here

http://sqlservertipsandtricks.blogspot.co.uk/2014/07/grouping-and-windowing-part-2.html