# Aggregation

## Revising Aggregations - The Count Function

{% embed url="<https://www.hackerrank.com/challenges/revising-aggregations-the-count-function?isFullScreen=true>" %}

```sql
select count(*) from CITY
where POPULATION > 100000;
```

## Revising Aggregations - The Sum Function

{% embed url="<https://www.hackerrank.com/challenges/revising-aggregations-sum?isFullScreen=true>" %}

```sql
select sum(POPULATION) from CITY
where DISTRICT = 'California';
```

## Revising Aggregations - Averages

{% embed url="<https://www.hackerrank.com/challenges/revising-aggregations-the-average-function?isFullScreen=true>" %}

```sql
select avg(POPULATION) from CITY
where DISTRICT = 'California';
```

## Average Population

{% embed url="<https://www.hackerrank.com/challenges/average-population?isFullScreen=true>" %}

```sql
select round(avg(POPULATION)) from CITY;
```

## Japan Population

{% embed url="<https://www.hackerrank.com/challenges/japan-population?isFullScreen=true>" %}

```sql
select sum(POPULATION) from CITY
where COUNTRYCODE = 'JPN';
```

## Population Density Difference

{% embed url="<https://www.hackerrank.com/challenges/population-density-difference?isFullScreen=true>" %}

```sql
select max(POPULATION) - min(POPULATION) from CITY;
```

## The Blunder

{% embed url="<https://www.hackerrank.com/challenges/the-blunder?isFullScreen=true>" %}

```sql
select ceil(avg(Salary) - avg(replace(Salary, '0', ''))) from EMPLOYEES;
```

## Top Earners

{% embed url="<https://www.hackerrank.com/challenges/earnings-of-employees?isFullScreen=true>" %}

```sql
select * from (
    select salary * months, count(*) from Employee
    group by salary * months
    order by 1 desc)
where rownum = 1;
```

## Weather Observation Station 2

{% embed url="<https://www.hackerrank.com/challenges/weather-observation-station-2?isFullScreen=true>" %}

```sql
select round(sum(LAT_N),2), round(sum(LONG_W),2) from STATION;
```

## Weather Observation Station 13

{% embed url="<https://www.hackerrank.com/challenges/weather-observation-station-13?isFullScreen=true>" %}

```sql
select round(sum(LAT_N), 4) from STATION
where LAT_N > 38.7880 and LAT_N < 137.2345;
```

## Weather Observation Station 14

{% embed url="<https://www.hackerrank.com/challenges/weather-observation-station-14?isFullScreen=true>" %}

```sql
select trunc(max(LAT_N),4) from STATION
where LAT_N < 137.2345;
```

## Weather Observation Station 15

{% embed url="<https://www.hackerrank.com/challenges/weather-observation-station-15?isFullScreen=true>" %}

```sql
select round(LONG_W,4) from STATION
where LAT_N = (select max(LAT_N) from STATION where LAT_N < 137.2345);
```

## Weather Observation Station 16

{% embed url="<https://www.hackerrank.com/challenges/weather-observation-station-16?isFullScreen=true>" %}

```sql
select round(min(LAT_N),4) from STATION
where LAT_N > 38.7780;
```

## Weather Observation Station 17

{% embed url="<https://www.hackerrank.com/challenges/weather-observation-station-17?isFullScreen=true>" %}

```sql
select * from (
    select round(LONG_W, 4) from STATION
    where LAT_N > 38.7780
    order by LAT_N asc)
where rownum = 1;
```

## Weather Observation Station 18

{% embed url="<https://www.hackerrank.com/challenges/weather-observation-station-18?isFullScreen=true>" %}

```sql
select round(((max(LAT_N) - min(LAT_N)) + (max(LONG_W) - min(LONG_W))),4) 
from STATION;
```

## Weather Observation Station 19

{% embed url="<https://www.hackerrank.com/challenges/weather-observation-station-19?isFullScreen=true>" %}

```sql
select round(sqrt(
        power((max(LAT_N) - min(LAT_N)),2) + 
        power((max(LONG_W) - min(LONG_W)),2)
),4)
from STATION;
```

## Weather Observation Station 20

{% embed url="<https://www.hackerrank.com/challenges/weather-observation-station-20?isFullScreen=true>" %}

```sql
select round(median(LAT_N),4) from STATION;
```