Functii de grup
Reguli:
campurile care apar in select in afara functiilor de grup trebuie sa apara in clauza GROUP BY
Exemplu:
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
Afiseaza cate o valoare pentru fiecare departament
Se poate folosi GROUP BY si intr-o comanda de join
SELECT department_id, department_name, max(salary)
FROM employees NATURAL JOIN departments
GROUP BY department_id, department_name
pot aparea in GRUP BY si alte coloane care nu apar in SELECT
SELECT MAX(salary)
FROM employees
BROUP BY departments
nu se pot folosi functii de grup in clauza WHERE
Sa se afiseze angajatii cu cel mai mic salariu din baza de date
SELECT *
FROM employees
WHERE salary=MIN(salary)
ð eroare
CORECT:
SELECT * FROM employees
WHERE salary = ( SELECT min(salary) FROM employees)
Alt exemplu:
SELECT * FROM employees
WHERE salary BETWEEN (SELECT min(salary) FROM employees)
AND (SELECT avg(salary)/2 FROM employees)
valorile NULL nu intra in calculul valorilor functiilor de grup
SELECT AVG(commission_pct), AVG(NVL(commission_pct,0)),
COUNT(commission_pct), SUM(commission_pct)
FROM employees
DISTINCT
Fie tabela USERS:
SELECT DISTINCT city
FROM users
SELECT DISTINCT LastName , city
FROM users
SELECT count(city) FROM users
=> 5
SELECT count(DISTINCT city) FROM users
=> 3
SELECT count(DISTINCT city, LastName) FROM users
=> EROARE, deoarece count nu poate avea decat un singur argument.
GROUP BY + HAVING
SELECT department_id, salary
FROM employees
WHERE department_id<80
ORDER BY department_id
SELECT
department_id, SUM(salary)
FROM employees
WHERE department_id<80
GROUP BY department_id
SELECT department_id, SUM(salary)
FROM employees
WHERE department_id<80
GROUP BY department_id
HAVING sum(salary)>5000
SELECT department_id, SUM(salary)
FROM employees
WHERE department_id<80
GROUP BY department_id
HAVING sum(salary)>5000
ORDER BY SUM(salary)
ORDINEA DE EXECUTARE A CLAUZELOR
|
