The γ (gamma) splits a relation into groups and computes aggregates:
text
dept_name γ avg(salary) as avg_salary (instructor)dept_namegroup by these attributesγgroupingavg(salary) as avg_salaryaggregate, with a name(instructor)the input
Textbooks write the grouping attributes as a small subscript on the left of γ and the aggregates on the right.
| Algebra | SQL |
|---|---|
dept_name γ avg(salary) as avg_salary (instructor) | SELECT dept_name, AVG(salary) AS avg_salary FROM instructor GROUP BY dept_name |
γ max(salary) (instructor) | SELECT MAX(salary) FROM instructor |
σ n > 1 (dept_name γ count(id) as n (instructor)) | … GROUP BY dept_name HAVING COUNT(id) > 1 |
Rules
- One tuple per group. With no attributes on the left, the whole relation is one group.
- The aggregate functions are
sum,avg,count,min,max. Some textbooks addcount-distinct(COUNT(DISTINCT …)in SQL). - An aggregate uses every value in the group, also repeated values. Two salaries of 60000 add 120000 to
sum(salary). asnames the aggregate attribute. Without a name, the attribute has no name; a later operator cannot refer to it.