SQL Union

[trojnfn]

I have a table by city, and population. I want to combine the populations of two cities into one. For example, the city of Ojai contains 10 people and the city of Santa Barbara contains 100 people. So for my output, I want to display the city as Santa Barbara and the population as 110

My query is something like this :

select sum(population), 'Santa Barbara' as city

from
(
select population from table where city='Ojai'
UNION
select populatoin from table where city='Santa Barbara'
)

It is giving me the population of the first record it finds, instead of both records.

Is using UNION not the right way to do it ?

I am going to try QofQ next.

[-__cfSearching__-]

> Is using UNION not the right way to do it ?

No. To understand why see:
http://www.w3schools.com/sql/sql_union.asp

[trojnfn]

Yes, I get that now.

I tried using QofQ and it gives me the same result, does not combine the two. What do I need to do to combine the population for both cities ?

[Newsgroup_User]

trojnfn wrote:
> Yes, I get that now.
>
> I tried using QofQ and it gives me the same result, does not combine the two. What do I need to do to combine the population for both cities ?

In the last minute before I go catch my train.

SELECT count(*)
FROM aTable
Where aKEY = 'one' OR aKEY = 'two'

[trojnfn]

I tried this later on and the where/or does give me everything for both cities. When attempt to sum, it is still giving me two sets, one for each city. I suspect it is because the city names are still different.

I want to do something like this so that both city names become one :
<cfif city="Ojai">
<cfset city='Santa Barbara'>
<cfelse>
<cfset city="#city#">
</cfif>

How do I do this inside sql ?

[Newsgroup_User]

in sql you can do this like:

SELECT sum(sq.population) AS ttl_population, 'Santa Barbara' AS city
FROM
(SELECT population FROM table WHERE city='Ojai' OR city='Santa
Barbara') AS sq


you can also do your math after the query using arraysum() cf function:

<cfquery name="myquery">
SELECT population
FROM table
WHERE city="A" or city="B"
</cfquery>
<cfoutput>#arraysum(myquery['population'])#</cfoutput>


Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[-__cfSearching__-]

> in sql you can do this like:

Well darn. In my haste, I misread the question. Disregard my comments as they do not really apply here.

[-__cfSearching__-]

> Yes, I get that now.

Hmm... did you read the top section of the article? It describes two types of unions ..