Count of items in a query

[m23]

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?

[blom0344]

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

[netanel]

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

[blom0344]

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..

[netanel]

I agree with you... But I understood he wanted it in the model...