Praktikum 5 Basis Data 

Aggregates

Aggregates can be combined with a WHERE clause to produce more complex results. For example, the query SELECT AVG(age) FROM friend WHERE age >= 21 computes the average age of people age 21 or older. This prevents Dick Gleason from being included in the average computation because he is younger than 21. NULL values are not processed by most aggregates, such as MAX(), SUM(), and AVG(); they are simply ignored. However, if a column contains only NULL values, the result is NULL, not zero. COUNT(*) is different in this respect.

Aggregate	Function
COUNT(*) SUM(colname) MAX(colname) MIN(colname) AVG(colname)	Count of rows total maximum minimum average

Contoh Aggregates

Connect ke database

Menampilkan table identitas berdasarkan nama depan .

Menjumlahkan dari keseluruhan baris

Menampilkan jumlah umur dari keseluruhan data yang ada

Menampilkan data dengan umur maksimal

Menampilkan data dengan umur minimal

Menampilkan rata-rata data yang ada

Tampilan nya sbb:

Using GROUP BY

 Simple aggregates return one row as a result. It is often desirable, however, to apply an aggregate to groups of rows. In queries using aggregates with GROUP BY, the aggregate is applied to rows grouped by another column in the table. For example, SELECT COUNT(*) FROM friend returns the total number of rows in the table. Using HAVING One more aggregate capability is often overlooked.the HAVING clause. HAVING allows a user to perform conditional tests on aggregate values. It is often employed in conjunction with GROUP BY. With HAVING, you can include or exclude groups based on the aggregate value for that group.

Aggregates and NULL values

Membuat tabel aggtest dan tampilannya sbb:

Menampilkan max column dari tabel aggtest:

Menampilkan  jumlah  data dari tabel aggtest: