Hi,
Does anyone know how to get a count of the number of times an item occurs in a column. For example if I have
table1
col1
item1
item1
item2
item2
item2
In Sql I would do
Select count (col1),col1
from table1
group by col1
What I would like is to have a calculated column in Framework manager that gives the count that would be returned for the above sql. So I would get the below
col1 calculated column
item1, 2
item1, 2
item2, 3
item2, 3
item2, 3
Anyone know which function to use to do this?
select the dataitem twice in your report and use the count aggregate type for the second dataitem.
You do not need to create them in the model, Cognos8 allows for flexible aggregate types.
However the output will be:
item1, 2
item2, 3
as it would be in SQL
You can do easily if you modify the SQL of the query subject. Like if your current SQL is:
SELECT Table1.Column1 from Table1
replace it with:
SELECT Table1.Column1, SubQuery1.CountCol
FROM Table1
INNER JOIN
(SELECT Column1, COUNT(Column1) AS CountCol
FROM Table1
GROUP BY Column1)
AS SubQuery1
ON
Table1.Column1 = SubQuery1.Column1
Correct use of aggregate types will yield the result. Why resort to straight SQL? You pay premium :D amounts for using a BI tool to generate the SQL for you..
I agree with you... But I understood he wanted it in the model...