The HAVING command is used to perform aggregate functions such as AVG,COUNT, and MAX in a group_by. Unlike WHERE, HAVING works specifically with grouped results.

For Example:

select salary, count(salary) from employees group by salary having count(salary) = 2;

Here, we are only returning salary and the count of the grouped salaryonly if the count is equal to 2. We are specifying which grouped items should be returned in the table employees. The count should be equal to 2.

Result:

In this example, all the grouped salary counts are 2. Thus, we have 2 employees earning 8200, 2 earning 4800, and 2 earning 17000.

Ecto query for having

In Ecto, having/3 filters grouped rows based on aggregate conditions.

having/3

Expression example

HR.Employee
|> group_by([c], c.salary)
|> having([c], count(c.salary)==2)
|> select([c], %{"salary" => c.salary, "count" => count(c.salary)})
|> HR.Repo.all()

having([c], count(c.salary)==2) -> [c] is the reference variable. Refer Aliases in Ecto to understand aliases/reference variables count(c.salary)==2 -> Here, the count function is used to specify that the count of the salary should be 2. We are specifying to return the grouped rows where the count of the salary is equal to 2.

Result:

Keyword example

HR.Repo.all(from c in HR.Employee, group_by: c.salary, having: count(c.salary)==2, select: %{"salary" => c.salary, "count" => count(c.job_id)})

select: is a key. e is a value that is a reference variable for HR.Employee.