GROUP BY - Aggregate Function İle Birlikte Kullanılır

Giriş
Group By önce çalışır. Daha sonra yapılan select işleminde herhangi bir satırın değeri seçilebilir.

Aggregation Function

Açıklaması şöyle. Yani GROUP BY bir aggregate metod ile birlikte kullanılır. Bunlar SUM(),COUNT(),AVG() olabilir.

The real importance of GROUP BY can be seen when you use it with aggregate functions like SUM(), COUNT()

Eğer Anlamlı Bir Aggregation Function Bulamadıysak

Örnek - MIN
Elimizde şöyle bir cümle olsun

select id, name, min(qty) from foo group by name;

Elimizde şöyle bir tablo olsun

+----+--------+-------+
| id |  name  |  qty  |
+----+--------+-------+
| 1  |  John  |   3   |
| 2  |  John  |   1   |
| 3  |  Mary  |   5   |
| 4  |  Mary  |   2   |
| 5  |  Gary  |   3   |
| 6  |  Gary  |   4   |
| 7  |  Gary  |   5   |
+----+--------+-------+

Çıktı olarak şunu alabiliriz. Mary'nin id değeri yanlış geliyor.

select id, name, min(qty) from foo group by name;
+----+------+----------+
| id | name | min(qty) |
+----+------+----------+
|  1 | John |        1 |
|  3 | Mary |        2 |
+----+------+----------+

Açıklaması şöyle.

As shown an id that is not related with the qty is in the result, why? Since you are grouping by name alone there are two id's to choose from for each name. MySQL will randomly pick an id for John and one for Mary from these groups, sometimes the result will be correct, sometimes incorrect. What you need to do is to choose the minimum qty for each name, then use that qty and the name to find the id associated with that.

Doğru sorgu şöyle. Önce en küçük qty değeri bulunur. Daha sonra dış sorgu ile bu join yapılır ve name bulunur.

SELECT id, name 
FROM (
    SELECT name, MIN(qty) minqty
    FROM foo fs
    GROUP BY name
) AS a
    JOIN foo f
        ON f.name = fs.name
        AND f.qty = fs.minqty

Örnek - MIN

Şöyle yaparız

SELECT towns.id, towns.town, peoples.names
FROM towns
LEFT JOIN
(
    SELECT MAX(names) AS names, postcode
    FROM peoples
    GROUP BY postcode
    HAVING COUNT(name) = 1
) peoples
ON towns.postcode = peoples.postcode

Açıklaması şöyle

When you use a GROUP BY clause, you have to use an aggregate function to select columns that aren't being grouped on. In this case, since you only care about the groups that have 1 value in them (i.e. the people with unique post codes) then it doesn't matter which aggregate function you use here, MAX or MIN, it'll always be the same 1 person.