UNION Query

[weezerboy]

I have a union query like this that gets info from 2 different tables

Like this

  <cfquery name="getpeople"   datasource="#request.dsn#"    >

SELECT  ID,CityName,  SUM(people) AS sump FROM table 1

GROUP by ID,CityName

UNION

  SELECT  ID,CityName,  SUM(people) AS sump FROM table 2

GROUP by ID,CityName

ORDER BY SUM(people)

</cfquery>

So what I want is the sums from both tables but what I get is the sums of each table.

For example I get

ID-CityName-sump

1- New York -3

1-New York- 4

2-Chicago -5

2-Chicago -6

What I want is
ID-CityName-sump

1-New York- 7

2-Chicago -11

Do I need to do another query after the union query?

[paross1]

Something like this?

SELECT  ID, CityName, SUM(people) AS sump

FROM (SELECT  ID, CityName, people

FROM table 1

UNION

SELECT  ID, CityName, people

FROM table 2)

GROUP by ID, CityName

ORDER BY SUM(people)