Q:

Nested Count query

I need some help for a query that outputs something like this:
CityName
CITY A
CITY B
CITY C
CITY A
CITY A
CITY A
CITY C

From this example, the count of each city is: CITY A = 4 , CITY B =1 , CITY C = 2
How can I add the (nested) Count of each CityName next to it so the output looks like this:
CityName   CityCount
CITY A              4
CITY B              1
CITY C              2
CITY A              4
CITY A              4
CITY A              4
CITY C              2

Any feedback would be more than appreciated!

I have the following table with the following fields:
CitiesList  --> CityName | Town | Date
An output of the table CitiesList looks like this:
CityName   |   Town      |      Date
CITY A      TOWN B      Jul 10, 2012
CITY A      TOWN D      Jul 10, 2012
CITY B      TOWN C      Jul 11, 2012
CITY C      TOWN C      Jul 11, 2012
CITY D      TOWN A      Jul 12, 2012
CITY D      TOWN C      Jul 13, 2012
CITY D      TOWN B      Jul 13, 2012
CITY E      TOWN C      Jul 13, 2012
.....
I want to add an extra field (TownCount) to this output with the number of times each TOWN appears in a time spam (Jul 10-Jul 13 in this example). The output should look like this:
CityName   |   Town      |      Date      |      TownCount
CITY A      TOWN B      Jul 10, 2012         2
CITY A      TOWN D      Jul 10, 2012         1
CITY B      TOWN C      Jul 11, 2012         4
CITY C      TOWN C      Jul 11, 2012         4
CITY D      TOWN A      Jul 12, 2012         1
CITY D      TOWN C      Jul 13, 2012         4
CITY D      TOWN B      Jul 13, 2012         2
CITY E      TOWN C      Jul 13, 2012         4
.....

A:

You provided no details on what kind of system you are using but in SQL you can use:
select t1.cityname, t2.CityCount
from yourtable t1
left join
(
    select count(*) CityCount, cityname
    from yourtable
    group by cityname
) t2
    on t1.cityname = t2.cityname

See SQL Fiddle with Demo
Based on your edit, it looks like you want to do the following:
select t1.cityname, t1.town, t1.date, t2.TownCount
from yourtable t1
left join
(
    select count(*) TownCount, Town
    from yourtable
    group by Town
) t2
    on t1.town = t2.town

see SQL Fiddle with Demo

