Union or Join or Nested Select - Can't Remember

[Knum]

Its been along time since I have had to write tsql from the hip so any help
would be greatly valued. I have a table that contains Country, State, City and
I need to COUNT the total records for each. Example: United States (10)
---Texas (6) -------Dallas (4) -------Huston (2) ---New York (4) -------New
York (1) -------Buffalo (3) United Kindom(8) ---England (6) -------London(4)
-------Cork(2) ---Scotland (2) -------Fife (2) Or another way of looking at the
output: United States (10) ------------ Texas (6) ------------ Dallas (4)
United States (10) ------------ Texas (6) ------------ Huston (2) United States
(10) ------------ New York (4) ------- New York (1) United States (10)
------------ New York (4) ------- Buffalo (3) United Kindom(8) ------------
England (6) --------- London(4) United Kindom(8) ------------ England (6)
--------- Cork(2) United Kindom(8) ------------ Scotland(2) --------- Fife(2)
I know I am missing something really simple and I have done it before but I
just can't remember (two plus years the brain has been on the back burner) As
you can see the group by clause blows it up, I can't use the cfoutput group by
ether do to the fact that I am dumping the output into CF_MultiRelatedSelects
SELECT country&' ('&count(country)&')' as cln, state&'
('&count(state)&')' as cls, city&' ('&count(city)&')' as
clc FROM peole GROUP BY country, state,city ORDER BY country, state,city

[zoeski80]

Hi Knum

I got these 2 working.

HTH

Zoe

QUERY 1
<CFQUERY NAME="queryName" ...>
SELECT country & '(' & count(country) & ')' as Country_Count,
'' as State_Count,
'' as City_Count
FROM peole
GROUP BY Country

UNION ALL

SELECT '' as Country_Count,
state & '(' & count(state) & ')' as State_Count,
'' as City_Count
FROM peole
GROUP BY State

UNION ALL

SELECT '' as Country_Count,
'' as State_Count,
city & '(' & count(city) & ')' as City_Count
FROM peole
GROUP BY City

ORDER BY Country_Count, State_Count, City_Count
</cfquery>

<CFDUMP VAR="#queryName#" LABEL="query 1">

QUERY 2
<CFQUERY NAME="queryName" ...>
SELECT 'Country' as FieldType, Country as FieldValue, count(country) as
FieldCount
FROM peole
GROUP BY country
UNION ALL
SELECT 'State' as FieldType, State as FieldValue, count(State) as FieldCount
FROM peole
GROUP BY State
UNION ALL
SELECT 'City' as FieldType, City as FieldValue, count(City) as FieldCount
FROM peole
GROUP BY City
ORDER BY FieldType, FieldValue, FieldCount
</cfquery>

<CFDUMP VAR="#queryName#" LABEL="query 2">