Aggregate Function

Teori Aggregate function

select count(*) from film;
- menghitung jumlah record (baris data) dari tabel film
select release_year, count(*) from film group by release_year;
- menampilkan banyak film untuk setiap tahun release
- kebetulan semua film direlease tahun 2006
bila menggunakan aggregate function dan group by
- setiap kolom yang ingin ditampilkan hendaknya ditulis di group by
- perhatikan contoh berikut:
  - di bagian select ada first_name dan last_name dan total bayar,
  - maka di bagian group by juga dituliskan sama (kecuali kolom yang ditotal)

  select customer.first_name, customer.last_name, sum(payment.amount) as total_bayar
  from customer
  inner join payment   ON customer.customer_id   = payment.customer_id
  group by customer.first_name, customer.last_name
  order by sum(payment.amount) DESC;

Soal

tampilkan berapa jumlah record customer
tampilkan berapa jumlah record country

tampilkan nama customer, beserta city dan countrynya seperti contoh berikut:

  first_name  |            city            |                country
 -------------+----------------------------+---------------------------------------
  Jared       | Purwakarta                 | Indonesia
  Mary        | Sasebo                     | Japan
  Patricia    | San Bernardino             | United States

tampilkan customer yang berasal dari Indonesia
tampilkan ada berapa orang customer yang berasal dari United States
- cukup nilainya saja misalnya 10 orang, tidak perlu data detilnya

tampilkan nama country dan jumlah customernya, dimana country yang
jumlah customer terbesar ditempatkan paling atas, seperti contoh berikut:

                 country                | jumlah_customer
 ---------------------------------------+-----------------
  India                                 |              60
  China                                 |              53

tampilkan nama country dan jumlah customernya,
dengan syarat jumlah customer pada country itu diatas 20
tampilkan semua nama film dan tanggal sewanya yang pernah disewa oleh Jorge Olivares (petunjuk: Jorge adalah first_name dari customer dan Olivares adalah last_name)
tampilkan total uang yang sudah dibayar oleh Jorge Olivares
(petunjuk gunakan table customer -> payment)

Jawab

soal 1
```
  select count(*) from customer;
```
soal 2
```
  select count(*) from country;
```

soal 3

select cu.first_name, c.city, co.country
from customer cu
inner join address a  ON cu.address_id = a.address_id
inner join city    c  ON a.city_id     = c.city_id
inner join country co ON c.country_id  = co.country_id;

soal 4

select cu.first_name, c.city, co.country
from customer cu
inner join address a  ON cu.address_id = a.address_id
inner join city    c  ON a.city_id     = c.city_id
inner join country co ON c.country_id  = co.country_id
where co.country = 'Indonesia';

soal 5

select count(*) as jumlah_customer
from customer cu
inner join address a  ON cu.address_id = a.address_id
inner join city    c  ON a.city_id     = c.city_id
inner join country co ON c.country_id  = co.country_id
where co.country = 'United States';

soal 6

select co.country, count(*) as jumlah_customer
from customer cu
inner join address a  ON cu.address_id = a.address_id
inner join city    c  ON a.city_id     = c.city_id
inner join country co ON c.country_id  = co.country_id
group by co.country
order by jumlah_customer DESC;

soal 7

select co.country, count(*) as jumlah_customer
from customer cu
inner join address a  ON cu.address_id = a.address_id
inner join city    c  ON a.city_id     = c.city_id
inner join country co ON c.country_id  = co.country_id
group by co.country
having count(*) > 20
order by jumlah_customer DESC;

soal 8

select f.title, r.rental_date
from customer cu
inner join rental r    ON cu.customer_id = r.customer_id
inner join inventory i ON i.inventory_id = r.inventory_id
inner join film f      ON f.film_id      = i.film_id 
where cu.first_name = 'Jorge' AND cu.last_name = 'Olivares';

soal 9

select cu.first_name, cu.last_name, sum(p.amount) as total_bayar
from customer cu
inner join payment p  ON cu.customer_id   = p.customer_id
group by cu.first_name, cu.last_name
order by sum(p.amount) DESC;