MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

 » Database Journal Home » Database Articles » Database Tutorials MS SQL Oracle DB2 MS Access MySQL » RESOURCES Database Tools SQL Scripts & Samples Tips » Database Forum » Slideshows » Sitemap

## MS SQL

Posted Mar 8, 2004

# MDX Essentials: Basic Numeric Functions: The Count() Function - Page 2

By William Pearson

### The Count Function

According to the Analysis Services Books Online, the Count function "returns the number of items in a collection, depending on the collection." The term "collection" here is generic, as the "collections" to which the Books Online refers can consist of the dimensions in a cube or a tuple, the cells in a set, or the levels in a dimension or a hierarchy. Count handles these "collections" in a couple of different syntactical ways. The scope of this article will be to focus more on the set-specific use of Count(), and we will refer to the function with the appended parenthesis ( "()" ) in that context.

We will examine the syntax for the Count() function, and then take a look at the options it affords with regard to the exclusion of empty cells from the counts it performs. Next, we will undertake practice examples built around hypothetical business needs that illustrate logical uses for the function. We will extend the examples to include exclusion of empty cells in a set we construct, to afford us a scenario for examining the options to include or exclude these cells in the overall count.

#### Discussion

The Count() function counts the cells defined by the set that is specified in the function. The syntax with which we employ the function affords us the option of specifying whether or not we wish to count empty cells within the range of the specified set. If we do not wish to include empties, we simply add the appropriate keyword to restrict the returned count to populated cells only.

Let's look at some syntax illustrations to further clarify the operation of Count().

#### Syntax

Syntactically, the set we use to specify the range of cells to count is placed within the parentheses to the right of Count, and separated by a comma from the optional flag we have described. The optional EXCLUDEEMPTY flag overrides the default behavior of the function (which is INCLUDEEMPTY) with regard to empty cells, and forces the function to ignore any unpopulated cells within the set specified.

The syntax is shown in the following string:

`Count(Set, [, INCLUDEEMPTY | EXCLUDEEMPTY])`

The following example expression illustrates a use of the Count() function. Let's say that FoodMart information consumers, whose data is housed within the Warehouse cube, wish to see the number of Warehouses supporting each of our Product Departments in a given year. The basic Count() function involved, disregarding for the time being the empty or non-empty consideration, would be as follows:

`Count ({[Warehouse].[City].MEMBERS })  `

This is simply expressing that we wish to count the "number of Warehouse City members" for a given purpose. Looking at our cube structure within the metadata pane of the MDX Sample Application, as shown in Illustration 1, we can verify that there are twenty-three Warehouse City locations by performing a manual count.

Illustration 1: We Verify Twenty - Three Warehouse Cities in the Warehouse Cube

If we were to create an MDX query, within which we construct a calculated member to perform the count (see Calculated Members: Introduction and Calculated Members: Further Considerations and Perspectives, among kindred articles, in the Database Journal MDX in Analysis Services series for a hands-on introduction to calculated members), and to obtain the information requested, we might begin with the following:

```WITH
Member [Warehouse].[No. Locations]
AS
'COUNT ( {[Warehouse].[City].MEMBERS })'
SELECT
{ [Warehouse]. [No. Locations] } ON COLUMNS,

{ [Product].[Product Department].Members} ON ROWS
FROM
[Warehouse]
WHERE
[Time].[1998]
```

This simple query, for a count of the Warehouse Cities by Product Department, would return a dataset similar to that depicted in Table 1.

 Product Department No. Locations Alcoholic Beverages 23 Beverages 23 Dairy 23 Baked Goods 23 Baking Goods 23 Breakfast Foods 23 Canned Foods 23 Canned Products 23 Dairy 23 Deli 23 Eggs 23 Frozen Foods 23 Meat 23 Produce 23 Seafood 23 Snack Foods 23 Snacks 23 Starchy Foods 23 Carousel 23 Checkout 23 Health and Hygiene 23 Household 23 Periodicals 23

Table 1: Results of a Simple Count of Warehouse Cities by Product Department

If we were to scrutinize the data, or perhaps to query the cube for the same information in a different manner, we would soon realize that the above results reflect every possible combination of Product Department and Warehouse City. This is because we have left the Count() function at default; not stipulating that we wished to leave empty cells (representing Product Department / Warehouse City combinations that witnessed no action), through the use of the EXCLUDEEMPTY keyword in our Count() function means that INCLUDEEMPTY flag is assumed by default.

To prove this, we merely add the EXCLUDEEMPTY keyword, flagging the function to ignore empty cells in the count, as follows:

`   COUNT ( {[Warehouse].[City].MEMBERS}, EXCLUDEEMPTY)`

Upon execution with the change above, the query would return a dataset similar to that depicted in Table 2.

 Product Department No. Locations Alcoholic Beverages 20 Beverages 23 Dairy 21 Baked Goods 23 Baking Goods 23 Breakfast Foods 22 Canned Foods 23 Canned Products 18 Dairy 23 Deli 23 Eggs 22 Frozen Foods 23 Meat 17 Produce 23 Seafood 16 Snack Foods 23 Snacks 22 Starchy Foods 20 Carousel 16 Checkout 19 Health and Hygiene 23 Household 23 Periodicals 18

Table 2: Results of a Count of Warehouse Cities by Product Department, Empty Cells Not Counted

We will practice the use of the Count() function in the section that follows. Moreover, we will explore the use of the EXCLUDEEMPTY flag we have discussed, to activate the concept of its use in eliminating empty cells from consideration by the function. Finally, we will go a step further, and confirm our results by looking at the data from another perspective.

MS SQL Archives

Comment and Contribute

(Maximum characters: 1200). You have characters left.

 Latest Forum Threads MS SQL Forum Topic By Replies Updated SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM Need help changing table contents nkawtg 1 August 17th, 03:02 AM SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM SQL Server – Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM