MySQL GROUP BY FUNKCIJA

Funkcija GROUP BY se dosta koristi radi "sredjivanja" rezultata.

GROUP BY komandu mozemo "ugnezditi" u, recimo, funkciju SUM() kojom vracamo ukupne iznose za svakog klijenta, ali pre toga da vidimo kako uopsteno izgleda GROUP BY:

SELECT naziv_kolone, funkcija(kolona)
FROM naziv_tabele
WHERE uslov
GROUP BY kolona

Da napravimo, radi primera, tabelu "Narudzbe" u kojima se neki klijenti pojavljuju vise puta, sto nam odgovara radi SUM-e njihovih narudzbi:

mysql> CREATE TABLE narudzbe (
    -> oznakainterno INT NOT NULL,
    -> iznos INT,
    -> narucio CHAR (30) );
Query OK, 0 rows affected (0.23 sec)

mysql>

mysql> describe narudzbe;
+---------------+----------+------+-----+---------+-------+
| Field         | Type     | Null | Key | Default | Extra |
+---------------+----------+------+-----+---------+-------+
| oznakainterno | int(11)  | NO   |     | NULL    |       |
| iznos         | int(11)  | YES  |     | NULL    |       |
| narucio       | char(30) | YES  |     | NULL    |       |
+---------------+----------+------+-----+---------+-------+
3 rows in set (0.06 sec)

mysql>

Tabelu cemo popuniti podacima preko INSERT-a (rucno).

mysql> INSERT INTO narudzbe (oznakainterno, iznos, narucio)
    -> VALUES ('43230', 4000, 'Zoran');
Query OK, 1 row affected (1.11 sec)

mysql> INSERT INTO narudzbe (oznakainterno, iznos, narucio)
    -> VALUES ('45280', 5000, 'Marko');
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO narudzbe (oznakainterno, iznos, narucio)
    -> VALUES ('45280', 5000, 'Jagoda');
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO narudzbe (oznakainterno, iznos, narucio)
    -> VALUES ('28900', 4000, 'Jagoda');
Query OK, 1 row affected (0.08 sec)

mysql> INSERT INTO narudzbe (oznakainterno, iznos, narucio)
    -> VALUES ('35420', 5600, 'Zoran');
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO narudzbe (oznakainterno, iznos, narucio)
    -> VALUES ('40680', 3800, 'Petar');
Query OK, 1 row affected (0.05 sec)

Idemo preko SUM funkcije izvuci totale za svakog narucioca posebno:

SELECT narucio, SUM(iznos) AS total
FROM narudzbe
GROUP BY narucio;

mysql> SELECT narucio, SUM(iznos) AS total
    -> FROM narudzbe
    -> GROUP BY narucio;
+---------+-------+
| narucio | total |
+---------+-------+
| Jagoda  |  9000 |
| Marko   |  5000 |
| Petar   |  3800 |
| Zoran   |  9600 |
+---------+-------+
4 rows in set (0.13 sec)

mysql>