# AGGREGATE Tutorial

## Country Profile

For these questions you should use `aggregate([])` on the collection `world`

You may find these AGGREGATE examples useful.

## Per Capita GDP

Give the `name` and the `per capita GDP` for those countries with a `population` of at least 200 million.

per capita GDP is the GDP divided by the population.

```db.world.aggregate([
{\$match: {
population: {\$gte: 250000000}
}},
{\$project: {
_id: 0,
name: 1,
"per capita GDP": {\$divide: ['\$gdp', 1000000]}
}}
]);```
`db.world.aggregate([{"\$match":{"population":{"\$gte":200000000}}},{"\$project":{"_id":0,"name":1,"per capita GDP": {"\$divide": ["\$gdp","\$population"]}}}]);`

## Population Density in South America

Give the `name` and the `population density` of all countries in South America.

population density is the population divided by the area

Use a `\$match`. `{"area":{"\$ne":0}}`

```db.world.aggregate([
{\$match: {continent: 'Asia'}},
{\$project: {
_id: 0,
name: 1,
density: {\$divide: ["\$population", "\$area"]}
}}
]);```
`db.world.aggregate([{\$match:{continent:'South America'}},{\$project:{_id:0,name:1,density:{\$divide:["\$population","\$area"]}}}]);`

## Population Density for "V"

Give the `name` and the `population density` of all countries with name after V in the alphabet.

Note that because Vatican City (with area 0) is in Europe you will get a divide by zero error unless you filter first.

Use a `\$match`.

```{
\$match: {
area: {
"\$ne": 0
}
}
}
```
```db.world.aggregate([
{\$match: {name: {\$gt: 'V'}}},
{\$project: {
_id: 0,
name: 1,
area: 1
}}
]);```
`db.world.aggregate([{\$match:{name:{\$gt:'V'}}},{\$match:{area:{"\$ne":0}}},{\$project:{_id:0,name:1,density:{\$divide:["\$population","\$area"]}}}]);`

## Population in millions

Show the `name` and `population` in millions for the countries of the continent South America. Divide the population by 1000000 to get population in millions.

```db.world.aggregate([
{\$match:{

}},
{\$project:{
_id: 0,
name: 1
}}
]);```
`db.world.aggregate([{"\$match":{"continent":{"\$eq":"South America"}}},{"\$project":{"_id":0,"name":1,"population":{"\$divide":["\$population",1000000]}}}]);`

## Population density

Show the `name` and `population density` for France, Germany, and Italy

```db.world.aggregate([
{\$match:{
name: {\$in: ['United Kingdom', 'United States', 'Brazil']},
population: {\$ne: null},
area: {\$ne: 0}
}},
{\$project:{
_id: 0,
name: 1
}}
]);```
`db.world.aggregate([{"\$match":{"name":{"\$in":['France','Germany','Italy']},"population":{"\$ne":null},"area":{"\$ne":0}}},{"\$project":{"_id":0,"name":1,"population density":{"\$divide":["\$population","\$area"]}}}]);`

## Continents by area

Order the `continents` by `area` from most to least.

```db.world.aggregate([
{\$group: {
_id: "\$name",
area: {\$max: "\$area"}
}},
{\$sort: {
area: -1
}},
{\$project: {
_id: 1,
area: 1
}}
]);```
`db.world.aggregate([{"\$group":{"_id":"\$continent","area":{"\$sum":"\$area"}}},{"\$sort":{"area":-1}},{"\$project":{"_id":1,"area":1}}]);`

## Big Continents

Show the only two continents with total area greater than 25000000 and then sort from largest to smallest.

```db.world.aggregate([
{\$match: {
continent: "North America"
}},
{\$project: {
_id: 0,
name: 1
}}
]);```
`db.world.aggregate([{\$group:{_id:"\$continent",area:{\$sum:"\$area"}}},{\$sort:{area:-1}},{\$match:{area:{\$gt:25000000}}}]);`

## First and last country by continent

For each continent show the first and last country alphabetically like this:

``` { "_id" : "Africa", "from" : "Algeria", "to" : "Zimbabwe" }
{ "_id" : "Asia", "from" : "Afghanistan", "to" : "Yemen" }
{ "_id" : "Caribbean", "from" : "Antigua and Barbuda", "to" : "Trinidad and Tobago" }
{ "_id" : "Eurasia", "from" : "Armenia", "to" : "Russia" }
{ "_id" : "Europe", "from" : "Albania", "to" : "Vatican City" }
{ "_id" : "North America", "from" : "Belize", "to" : "United States" }
{ "_id" : "Oceania", "from" : "Australia", "to" : "Vanuatu" }
{ "_id" : "South America", "from" : "Argentina", "to" : "Venezuela" }
```
```db.world.aggregate([
{\$group: {
_id: "\$continent"
}},
{\$sort: {
_id: 1
}}
]);```
`db.world.aggregate([{\$sort:{name:1}},{\$group:{_id:'\$continent',from:{\$first:'\$name'},to:{\$last:'\$name'}},},{\$sort:{_id:1}}])`

## Countries beginning with...

Group countries according to the first letter of the name. As shown. Only give "U" through to "Z".

You will need to use the \$substr function and the \$push aggregate function.

``` { "_id" : "U", "list" : [ "Uganda", "Ukraine", "United Arab Emirates", "United Kingdom", "United States", "Uruguay", "Uzbekistan" ] }
{ "_id" : "V", "list" : [ "Vanuatu", "Vatican City", "Venezuela", "Vietnam" ] }
{ "_id" : "Y", "list" : [ "Yemen" ] }
{ "_id" : "Z", "list" : [ "Zambia", "Zimbabwe" ] }
```
```db.world.aggregate([
{\$project: {
_id: '\$name',
startsWith: {\$substr: ['\$name', 0, 1]}
}},
{\$match: {
_id: {\$gte: 'U'}
}},
{\$sort: {_id: 1}}
]);```
`db.world.aggregate([{\$group:{_id:{\$substr:['\$name',0,1]},list:{\$push:'\$name'}}},{\$match:{_id:{\$gte:'U'}}},{\$sort:{_id:1}}]);`

## Messing with continent names

Combine North America and South America to America, and then list the continents by area. Biggest first.

```db.world.aggregate([
{\$group: {
_id: {
\$cond: [
{\$eq: ["\$continent", "North America"]},
"America",
{\$cond: [
{\$eq: ["\$continent", "Asia"]},
"The East",
"\$continent"
]}
]
},
area: {\$sum: "\$area"}
}},
{\$sort: {area: -1}},
{\$project: {
_id: 1,
area: 1
}}
]);```
`db.world.aggregate([{"\$group":{"_id":{"\$cond":[{"\$eq":["\$continent","South America"]},"America",{"\$cond":[{"\$eq":["\$continent","North America"]},"America","\$continent"]}]},"area":{"\$sum":"\$area"}}},{"\$sort":{"area":-1}},{"\$project":{"_id":1,"area":1}}]);`

## Messing with continent names 2

Show the name and the continent for countries beginning with N - but replace the continent Oceania with Australasia.

```db.world.aggregate([
{\$match: {
name: {\$regex: "^N"}
}},
{\$project: {
_id: 0,
name: 1
}}
]);```
`db.world.aggregate([{"\$match":{"name":{"\$regex":"^N"}}},{"\$project":{"_id":0,"name":1,"continent":{"\$cond":[{"\$eq":["\$continent","Oceania"]},"Australasia","\$continent"]}}}]);`

## Messing with continent names 3

Show the name and the continent but:

• substitute Eurasia for Europe and Asia.
• substitute America - for each country in North America or South America or Caribbean.

Only show countries beginning with A or B

If you're struggling you may want to experiment with `\$and`,`\$or`, etc.

`db.world.aggregate([{\$match:{name:{\$regex:"^A|^B"}}},{\$project:{_id:0,name:1,continent:{\$cond:[{\$or:[{\$eq:["\$continent","Europe"]},{\$eq:["\$continent","Asia"]}]},"Eurasia",{\$cond:[{\$or:[{\$eq:["\$continent","North America"]},{\$eq:["\$continent","South America"]},{\$eq:["\$continent","Caribbean"]}]},"America","\$continent"]}]}}}]);`

## Messing with continent names 4

Put the continents right...

• Oceania becomes Australasia
• Countries in Eurasia and Turkey go to Europe/Asia
• Caribbean islands starting with 'B' go to North America, other Caribbean islands go to South America

Show the name, the original continent and the new continent of all countries.

`db.world.aggregate([{"\$project":{"_id":0,"name":1,"original":"\$continent","new":{"\$cond":[{"\$or":[{"\$eq":["\$continent","Eurasia"]},{"\$eq":["\$name","Turkey"]}]},"Europe/Asia",{"\$cond":[{"\$eq":["\$continent","Oceania"]},"Australasia",{"\$cond":[{"\$and":[{"\$eq":["\$continent","Caribbean"]},{"\$eq":[{"\$substr":["\$name",0,1]},"B"]}]},"North America",{"\$cond":[{"\$and":[{"\$eq":["\$continent","Caribbean"]},{"\$ne":[{"\$substr":["\$name",0,1]},"B"]}]},"South America","\$continent"]}]}]}]}}}]);`

Survey