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!
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
thanks a lot...it worked!!