Columns counting in Cognos Report Studio

[mycognosangel]

Hi I have the following table
Zone      City
AAA      Vegas
BBB      Dallas
CCC      Ohio
AAA      New York
AAA      Vegas
CCC      Ohio
CCC      Tokyo

I want to create a count column to count zone for distinct city, I want to generate this output. Pls which logic can I use to accomplish this count

Zone      City                  Count
AAA      Vegas              2
BBB      Dallas              1
CCC      Ohio                      2
AAA      New York                   1
AAA      Vegas              2
CCC      Ohio                      2
CCC      Tokyo              1

[BigChris]

count([Zone] for [City])

[mycognosangel]

Hi
I tried count([Zone] for [City]) and set the Agregation function and Roll up Aggregation fucntion to none, but it didnt work. I also used count(distinct [Zone] for [City]) with both functions to none, it still didnt work

[BigChris]

In what sense "didn't work"? What sort of results did you get?

[mycognosangel]

It partially worked the other way round as in: count(distinct [City] for [Zone]), while setting the both aggregation functions to NONE. But I need a final help which will fix the problem totally. I want to create some columns in a query. e.g Based on a query like this:


Query SampleA

Zone         Year         Country         Link
AAA           2000          NG              001
BBB           2001          US              009
CCC           2009          UK              004.

So I want to create a Query SampleB based on query SampleA in order to retrieve two columns. Ofcourse I could do it easily with SQL, but could a good samaritan be kind to let me know the detailed procedure for retrieving this information as a query based on the initial query?

Zone                 Country         
AAA                    NG             
BBB                    US               
CCC                    UK