Grupisanje podataka i grupne funkcije (Grouping data and group functions)

Ako je potrebno da znate koliko redova ispunjava odredjeni uslov ili koja je prosecna vrednost odredjene kolone, npr. prosecna vrednost po porudzbini. Za takve upite u MySQL-u postoje grupne funkcije (en. aggregate functions).

Ako zelite da znate prosecnu vednost porudzbine, mozete upotrebiti sledeci upit.

Primer 1 (ovaj fajl sacuvajte kao grupne-funkcije.sql):

select avg(iznos)
from porudzbine;

1. Pokrenite XAMPP. Kliknite na Admin u liniji MySQL-a ili ukucajte u address bar http://localhost/phpmyadmin/

2. Sa leve strane izaberite bazu podataka volimmotore, a sa desne izberite karticu SQL.
3. Ubacite ovaj kod i kliknite dugme "Go".

Da biste dobilli detaljnije podatke, upotrebite odredbu group by, koja omogucava da izracunate prosecnu vrednost porudzbina po odredjenim grupama podataka, npr. po sifri kupca.

Primer 2:

select sifrakupca, avg(iznos)
from porudzbine
group by sifrakupca;

4. Kliknite ponovo na karticu SQL, unesite primer 2 i klik na dugme "Go".

Ako upotrebite ovu odredbu zajedno sa nekom grupnom funkcijom, odredba group by zapravo menja ponasanje te funkcije. Umesto da izracuna prosecnu vrednost  svih porudzbina  u tabeli, upit u tom slucaju daje prosecnu vrednost  porudzbine po kupcu  (tj. po svakoj sifri kupca u koloni sifrakupca).

Sve kolone koje navedete u odredbi group by morate navesti i u odredbi select.

Rezultat grupne funkcije mozete porediti sa zadatom vrednoscu pomocu odredbe having. Ta odredba pise se neposredno iza odredbe group by i slicna je odredbi where, ali se odnosi samo na grupe podataka i rezultate grupnih funkcija,

Ako zelite da saznate koji su kupci poslali porudzbine cija prosecna vrednost prelazi 5000 eura.

Primer 3:

select sifrakupca, avg(iznos)
from porudzbine
group by sifrakupca
having avg(iznos) > 5000;


5. Kliknite ponovo na karticu SQL, unesite primer 3 i klik na dugme "Go".

Objasnjenje:

Odredba having  odnosi se na rezultat grupne funkcije.

Pogledajte grupne funkcije.

If you need to know how many rows meet the specified condition or what is the average value of a particular column, for example, the average value per order. For such queries in MySQL there are group functions (aggregate functions).

If you want to know the average of order, you can use the following query.

Example 1 (this file save as group-functions.sql):

SELECT AVG(amount)
FROM orders;

1. Start XAMPP. Click on the Admin in line MySQL or type in the address bar http://localhost/phpmyadmin/

2. From the left select the database lovebikes, and on the right select the SQL tab.

3. Insert this code and click "Go".

To get more detailed information, use the group by clause, which allows to calculate the average value of orders by certain groups of data, eg. by the customerid.

Example 2:

SELECT customerid, avg(amount)
FROM orders
GROUP BY customerid;

4. Click again on the SQL tab, enter example 2 and click on "Go".

If you use this clause, together with some group function, clause group by is actually changing behavior of that function. Instead of calculating the average value of all orders in the table, query in this case gives the average value of orders per customer (ie. for each customerid in column customerid).

All columns that are specified in the clause group by must be specified in clause select.

The result of group functions can be compared to the setpoint value by means of clause having. That clause you write directly behind the clause group by and it is similar to the WHERE clause, but applies only to group of the data and results of group functions,

If you want to know which customers submited orders whose average value exceeding 5,000 euros.

Example 3:

SELECT customerid, avg(amount)
FROM orders
GROUP BY customerid 
HAVING AVG(amount) > 5000;

5. Click again on the SQL tab, enter example 3 and click on "Go".

Explanation:

The clause having refers to a result of group functions.

Take a look at group functions.