Search

In my report, how can I count the number of records that meet a certain criteria?

Environment:

Analytics
Oracle B2C Service

Resolution:

Sometimes when creating reports, you need to report the count of records that meet a specific condition, such as the number of incidents in a certain status, or the number of incidents associated with a particular product, or those with solution times under a certain amount of time.

To count records with a specific condition, you can embed an IF clause within a SUM statement as the expression for your output column. The expression is:

sum(if(<conditions of what you are counting>, 1, 0))

This expression says that if the condition is met, then count it, otherwise do not count it. Basically, whenever a record meets the conditions, it tallies a "1". Then, the sum statement adds up all the "1" values to get the count and that overall count is reported in the output column.

For example, the expressions below can be used in several ways:

	sum(if(incidents.status_type='Unresolved'| incidents.status_type='Waiting', 1, 0))  This counts how many incidents are Waiting or Unresolved.
	sum(if(incidents.prod_lvl1='Fruit' & incidents.prod_lvl2='Apple', 1, 0)) This counts how many incidents are associated with the product value of Fruit and the sub-product Apple.
	sum(if(date_diff(incidents.closed, incidents.created) < 86400, 1, 0)) This counts how many incidents were solved within 1 day (86400 seconds).

For additional information, refer to the 'Managing output columns > Functions' section in the Online Help User Guide documentation. To access Oracle B2C Service manuals and documentation online, refer to the Documentation for Oracle B2C Service Products.