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
count([Zone] for [City])
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
In what sense "didn't work"? What sort of results did you get?
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