count employees based on flag in same query

[nsaxena]

Hi All ,

I have a employee table having field like employee,country and flag.Suppose flag has 3 distinct values(a,b,c)

I have to 1) count number of employees where flag=a and 2) count number of employees where flag=b 3) total number of employee(irrespective of flag)

How can i do this in single query(i dont want to create three queries each having 3 filters for each flag,and then do a join). Is there a better way to achieve this in single query.

Thanks!

[cognos810]

Hello nishantsaxena,
Create three data items in the same query...
1) CASE [FLAG] WHEN 'a' THEN 1 ELSE 0 END, then set the aggregate property of this data item to total
2) CASE [FLAG] WHEN 'b' THEN 1 ELSE 0 END, then set the aggregate property of this data item to total
3) CASE [FLAG] WHEN 'c' THEN 1 ELSE 0 END, then set the aggregate property of this data item to total
4) Finally, depending on your data, if all employees have one and only one flag, then create a data item that adds up the above three counts, OR create a data item with expression [EMPLOYEE] and then set its aggregation to Count Distinct.

-Cognos810

[nsaxena]

thanks a lot...it worked!!