Communities
A collaborative platform to connect and grow with like-minded Informaticans across the globe
Product Communities
Connect and collaborate with Informatica experts and champions
Discussions
Have a question? Start a Discussion and get immediate answers you are looking for
User Groups
Customer-organized groups that meet online and in-person. Join today to network, share ideas, and get tips on how to get the most out of Informatica
Get Started
Community Guidelines
Knowledge Center
Troubleshooting documents, product guides, how to videos, best practices, and more
Knowledge Base
One-stop self-service portal for solutions, FAQs, Whitepapers, How Tos, Videos, and more
Support TV
Video channel for step-by-step instructions to use our products, best practices, troubleshooting tips, and much more
Documentation
Information library of the latest product documents
Velocity (Best Practices)
Best practices and use cases from the Implementation team
Learn
Rich resources to help you leverage full capabilities of our products
Trainings
Role-based training programs for the best ROI
Certifications
Get certified on Informatica products. Free, Foundation, or Professional
Product Learning Paths
Free and unlimited modules based on your expertise level and journey
Resources
Library of content to help you leverage the best of Informatica products
Tech Tuesdays Webinars
Most popular webinars on product architecture, best practices, and more
Product Availability Matrix
Product Availability Matrix statements of Informatica products
SupportFlash
Monthly support newsletter
Support Documents
Informatica Support Guide and Statements, Quick Start Guides, and Cloud Product Description Schedule
Product Lifecycle
End of Life statements of Informatica products
Ideas
Events
Change Request Tracking
Marketplace
English
  • Informatica Data Quality 10.5.6
  • All Products

Table of Contents

Search

  1. Preface
  2. The Transformation Language
  3. Constants
  4. Operators
  5. Variables
  6. Dates
  7. Functions

Transformation Language Reference

Transformation Language Reference

Back Next

COUNT

COUNT

Returns the number of rows that have non-null values in a group. Optionally, you can include the asterisk (*) argument to count all input values in a transformation. You can nest only one other aggregate function within COUNT. You can apply a condition to filter rows before counting them.

Syntax

COUNT( 
value
 [, 
filter_condition
] )
or 
COUNT( 
*
 [, 
filter_condition
] )
The following table describes the arguments for this command:
Argument
Required/
Optional
Description
value
Required
Any datatype except Binary. Passes the values you want to count. You can enter any valid transformation expression.
*
Optional
Use to count
all rows
 in a transformation.
filter_condition
Optional
Limits the rows in the search. The filter condition must be a numeric value or evaluate to TRUE, FALSE, or NULL. You can enter any valid transformation expression.

Return Value

Integer.
0 if all values passed to this function are NULL or no rows are selected, unless you include the asterisk argument.

Nulls

If all values are NULL, the function returns 0.
If you apply the asterisk argument, this function counts all rows, regardless if a column in a row contains a null value.
If you apply the
value
 argument, this function ignores columns with null values.

Group By

COUNT groups values based on group by ports you define in the transformation, returning one result for each group. If there is no group by port COUNT treats all rows as one group, returning one value.

Examples

The following expression counts the items with less than 5 quantity in stock, excluding null values: 
COUNT( ITEM_NAME, IN_STOCK < 5 ) 
ITEM_NAME
IN_STOCK
 
Flashlight
10
NULL
2
Compass
NULL
Regulator System
5
Safety Knife
8
Halogen Flashlight
1
RETURN VALUE:
 1
In this example, the function counted the Halogen flashlight but not the NULL item. The function counts all rows in a transformation, including null values, as illustrated in the following example: 
COUNT( *, QTY < 5 ) 
ITEM_NAME
QTY
 
Flashlight
10
NULL
2
Compass
NULL
Regulator System
5
Safety Knife
8
Halogen Flashlight
1
RETURN VALUE:
 2
In this example, the function counts the NULL item and the Halogen Flashlight. If you include the asterisk argument, but do not use a filter, the function counts all rows that pass into the transformation. For example: 
COUNT( * ) 
ITEM_NAME
QTY
 
Flashlight
10
NULL
2
Compass
NULL
Regulator System
5
Safety Knife
8
Halogen Flashlight
1
RETURN VALUE:
 6

COUNT and Complex Data Types

You can use COUNT to count the number of rows in a complex port of type array or struct.
For example, you have the following array: 
emp_phones =
[205-128-6478, 722-515-2889]
[107-081-0961, 718-051-8116]
[344-894-6463, 861-411-8361]
[107-031-0961, NULL]
You can use the following expression to count the number of rows in the array port: 
COUNT( emp_phones )
RETURN VALUE
: 4
0 COMMENTS
We’d like to hear from you!