writing a simple join with sum function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

writing a simple join with sum function

Author

Topic

idb65
Starting Member

4 Posts

Posted - 2013-04-30 : 17:18:30

Hello All

i have these to tables

country: name,population
borders: country1,country2
every country have some neghbors, in borders table,
now i should calculate sum of pupulation of all neighbors of every country
thank u so much for ur help
I use oracle 11g

i wrote this but im sure that its pretty incorrect

select co.name,sum(co2.population)
from country co, borders bo, country co2
where co.name=bo.country1;

best

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-04-30 : 22:34:16

Here is a solution to get what you need using Microsoft SQL queries. (BTW this is MSSQL Forum)
The actual query might work on Oracle 11g as well. (I don't have Oracle installed to test this query)
[CODE]

-- START TABLE CREATION & DATA POPULATION
if OBJECt_ID('dbo.Country', 'U') IS NOT NULL DROP TABLE dbo.Country;
CREATE TABLE [dbo].[Country](
Name NVARCHAR(20),
PopulationNum INT);

if OBJECt_ID('dbo.Borders', 'U') IS NOT NULL DROP TABLE dbo.Borders;
CREATE TABLE [dbo].[Borders](
Country1 NVARCHAR(20),
Country2 NVARCHAR(20));

INSERT INTO Country(Name, PopulationNum) VALUES
('China', 2004500),
('Pakistan', 500220),
('Afghanistan', 60000),
('USA', 400000),
('Canada', 100034),
('Mexico', 506002),
('Bangladesh', 10012),
('India', 1000657);

INSERT INTO Borders(Country1, Country2)
VALUES ('India', 'Pakistan'),
('China', 'Pakistan'),
('India', 'Bangladesh'),
('Afghanistan', 'Pakistan'),
('USA', 'Canada'),
('USA', 'Mexico');

-- END TABLE CREATION & DATA POPULATION

SELECT F.CountryOfInterest, SUM(F.PopulationNum) as NeighborTotal from
(SELECT CB.CountryOfInterest, CB.BorderCountry, C.PopulationNum from
(SELECT Country1 as CountryOfInterest, Country2 as BorderCountry from Borders
UNION
SELECT Country2 as CountryOfInterest, Country1 as BorderCountry from Borders) as CB,
Country C where CB.BorderCountry = C.Name) as F
group by F.CountryOfInterest;

[/CODE]

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-01 : 10:42:45

If you have symmetric data as you described in http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=184983.
The following query should work.

[CODE]

SELECT CB.Country1, SUM(C.Population) as NeighborTotal from
Borders as CB, Country C where CB.Country2 = C.Name
group by CB.Country1;

[/CODE]

The query I posted earlier assumes asymmetric data (with no duplicate information).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources