Merhaba Arkadaslar
Bu bolumde Aggregate Function’lari , Group By ve Having kullanimini inceleyecegiz.  Burada ilgili konular ve keywordler icin bilgi vermeyecegim. Bunun icin Oracle DB yazilarima bakabilirsiniz. Burada konumuz JPQL , SQL degil.

Aggregate Functions

Aggregate Function’lar ;

• SUM
• AVG
• COUNT
• MAX
• MIN

SUM fonksiyonu, ilgili numerik alanin toplam degerini verir.
AVG fonksiyonu , ilgili numerik alanin ortalama degerini hesaplar (average).
COUNT fonksiyonu ilgili kayit sayisi icin kullanilabilir.
MAX fonksiyonu ilgili numerik alan icin en buyuk degeri dondurur.
MIN fonksiyonu ilgili numerik alan icin en kucuk degeri dondurur.

SELECT AVG(e.salary) , SUM(e.salary) , COUNT(e.id) , MAX(e.salary) , MIN(e.salary) FROM Employee30 e

System.out.println("AGGREGATE_FUNCTIONS");
List<Object[]>aggregateFunctions= jpqlService.executeResultListQuery(AGGREGATE_FUNCTIONS, Object[].class);
 
for (Object[] element : aggregateFunctions) {
    System.out.println(element[0] + " " + element[1]+ " " + element[2] + " " + element[3] + " " +element[4]);
}

Ornegimizi calistirdigimizda ;

AGGREGATE_FUNCTIONS
48846.153846153844 635000 13 62000 35000

GROUP BY

Departmana gore ortalama/AVG maas bilgisini getirmek istersek;

SELECT d.name , AVG(e.salary) FROM Department5 d JOIN d.employees e GROUP BY d.name

SQL karsiligi ;

SELECT d.name , AVG(e.salary)
FROM Department5 d JOIN Employee30 e
ON d.id = e.dept_id
GROUP BY d.name;

System.out.println("AVG_SALARY_GROUP_BY_DEPARTMENT");

List<Object[]> avgSalaryGroupByDepartment = jpqlService.executeResultListQuery(AVG_SALARY_GROUP_BY_DEPARTMENT, Object[].class);

for (Object[] element : avgSalaryGroupByDepartment) {
    System.out.println(element[0] + " " + element[1]);
}

AVG_SALARY_GROUP_BY_DEPARTMENT
Accounting 35000.0
QA 50000.0

HAVING BY

SELECT d.name , AVG(e.salary) FROM Department5 d JOIN d.employees e GROUP BY d.name HAVING AVG(e.salary)>?1";

SQL karsiligi olarak ;

SELECT d.name , AVG(e.salary)
FROM Department5 d JOIN Employee30 e
ON d.id = e.dept_id
GROUP BY d.name
HAVING AVG(e.salary)>50000;

Ornegimizi calistirdigimizda ;

System.out.println("AVG_SALARY_GROUP_BY_HAVING_DEPARTMENT");

List<Object[]> avgSalaryGroupByDepartmentHaving = jpqlService.executeResultListQuery(AVG_SALARY_GROUP_BY_DEPARTMENT_HAVING, Object[].class,50000);

for (Object[] element : avgSalaryGroupByDepartmentHaving) {
    System.out.println(element[0] + " " + element[1]);
}

ORDER BY

Employee kayitlari icin salary degerine gore buyukten kucuge dogru siralamak istersek ;

SELECT e FROM Employee30 e ORDER BY e.salary DESC

SQL karsiligi ;

SELECT * FROM Employee30 e ORDER BY e.salary DESC

System.out.println("SELECT_EMPLOYEE_ORDER_BY_SALARY");

List<Employee30> selectEmployeeOrderBySalary = jpqlService.executeResultListQuery(SELECT_EMPLOYEE_ORDER_BY_SALARY, Employee30.class);

for (Employee30 employee : selectEmployeeOrderBySalary) {
    System.out.println(employee);
}

Ornegimizi calistirdigimizda Employee kayitlari icin salary degerine gore DESCENDING olarak siralandigini gorebiliriz;

SELECT_EMPLOYEE_ORDER_BY_SALARY
Employee30 [id=6, name=Rod , surname=Johnson , salary=62000]
Employee30 [id=5, name=Scott, surname=Brown, salary=60000]
Employee30 [id=10, name=Sarah, surname=Proval, salary=59000]
Employee30 [id=1, name=John, surname=Robbins, salary=55000]
Employee30 [id=7, name=Sue, surname=Bellows, salary=54000]
Employee30 [id=2, name=Rob, surname=Freeman, salary=53000]
Employee30 [id=9, name=Jennifer, surname=Libby, salary=52000]
Employee30 [id=8, name=Stephanie, surname=Whitmore, salary=45000]
Employee30 [id=13, name=Jack, surname=Snooze, salary=43000]
Employee30 [id=4, name=Frank, surname= Sadler, salary=41000]
Employee30 [id=3, name=Peter, surname=Gunton, salary=40000]
Employee30 [id=12, name=Joe, surname=Hatlen, salary=36000]
Employee30 [id=11, name=Marcus, surname=Ragno, salary=35000]

UPDATE

Ornek olarak id degeri 1 olan Employee icin salary degerini guncellemek istersek ;

UPDATE Employee30 e SET e.salary=80000 WHERE e.id=1

DELETE

Ornek olarak id degeri 13 olan Employee kaydini silmek istersek ;

DELETE FROM Employee30 e WHERE e.id=13

Kaynak kodlar : Injavawetrust.jpa
Yazimi burada sonlandiriyorum.
Herkese Bol Javali Gunler dilerim.
Be an oracle man , import java.*;
Levent Erguder
OCP, Java SE 6 Programmer
OCE, Java EE 6 Web Component Developer