Unknown aggregate column in having clause

unknown column in 'having clause
having not working with pagination laravel
mysql having clause
having sql
having clause in oracle
wherebetween laravel
not having sql
laravel eloquent group by

im trying to build a location based event search in PHP + MySQL (Using Laravel and its Eloquent ORM)

This is the query I am using:

select 
    events.*, 
    ( 3959 * acos( cos( radians(50.5) ) * cos( radians( addresses.latitude ) ) * cos( radians( addresses.longitude ) - radians(9.50) ) + sin( radians(50.5) ) * sin( radians( addresses.latitude ) ) ) ) AS distance

 from 
    `events` inner join `addresses` on `events`.`address_id` = `addresses`.`id`
 having 
    `distance` <= 10 
 order by 
    `id` desc limit 15 offset 0

Im wondering why this error pops up, even though the distance column is in the "select statement".

"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'distance' in 'having clause' (SQL: select count(*) as aggregate from `events` inner join `addresses` on `events`.`address_id` = `addresses`.`id` having `distance` <= 10 order by `id` desc)"

This is the PHP Code I use to add the scope to the base query:

    $selectDistance =
        '( 3959 * acos( cos( radians(' . $latitude . ') ) ' .
        '* cos( radians( addresses.latitude ) ) ' .
        '* cos( radians( addresses.longitude ) - radians(' . $longitude . ') ) ' .
        '+ sin( radians(' . $latitude . ') ) ' .
        '* sin( radians( addresses.latitude ) ) ) ) AS distance';

    $query->select(DB::raw('events.*, ' . $selectDistance));
    $query->join('addresses', 'events.address_id', '=', 'addresses.id');
    $query->having('distance', '<=', $km);

Thank you very much :)

From the error message:

"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'distance' in 'having clause' (SQL: select count(*) as aggregate from events inner join addresses on events.address_id = addresses.id having distance <= 10 order by id desc)"

We can get the following

SQL:
select count(*) as aggregate 
from `events` inner join `addresses` on `events`.`address_id` = `addresses`.`id` 
having `distance` <= 10 
order by `id` desc

distance is not in the select list.

Eloquent queries break with having clauses · Issue #8436 · laravel , SQLSTATE[42S22]: Column not found: 1054 Unknown column 'distance' in '​having clause' (SQL: select count(*) as aggregate from gyms inner  Instead, you must use the aggregate function expression in the HAVING clause explicitly as follows: SELECT column_name1, column_name2, aggregate_function (column_name3) alias FROM table_name GROUP BY column_name1, column_name2 HAVING aggregate_function (column_name3) > value; SQL Server HAVING examples

The HAVING clause was introduced to SQL because we cannot use aggregate functions with WHERE keyword. According to your query there is no aggregate function within the SELECT statement or the HAVING clause. Therefore try to change

having 
    `distance` <= 10 

to

where
    `distance` <= 10 

The HAVING clause should be used in below format.

HAVING aggregate_function(column_name) operator value

Where operator can be =, >, < , <=, ect....

Furthermore your PHP code also need to be changed accordingly.

Below SQL tutorial link might help you to better understand the use of HAVING clause in SQL.

http://www.w3schools.com/sql/sql_having.asp

SQL Server HAVING clause with the SUM(), This tutorial shows you how to use the SQL Server HAVING clause to filter the groups for which the condition evaluates to FALSE or UNKNOWN are filtered out. to the aggregate function specified in the select list by using the column alias. Also, note (from the MySQL man): The SQL standard requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions. However, MySQL supports an extension to this behavior, and permits HAVING to refer to columns in the SELECT list and columns in outer subqueries as well.

You can try something like that

$selectDistance =
    '( 3959 * acos( cos( radians(' . $latitude . ') ) ' .
    '* cos( radians( addresses.latitude ) ) ' .
    '* cos( radians( addresses.longitude ) - radians(' . $longitude . ') ) ' .
    '+ sin( radians(' . $latitude . ') ) ' .
    '* sin( radians( addresses.latitude ) ) ) )'; 

$query->join('addresses', 'events.address_id', '=', 'addresses.id')
      ->select(DB::raw('events.*))
      ->selectRaw("{$selectDistance} AS distance")
      ->whereRaw("{$selectDistance} < ?", 10)
      ->get();

Using having without group by, calculate values for the table as a single group, not for groups within the table. GROUP By clause apply on name column with use AVG aggregate function to average the total working hours divide by number of day. Also with HAVING clause condition allow only avg of hours grater than 8.

SQL: HAVING Clause, clause only to columns that also appear in the GROUP BY clause or in an aggregate function. The HAVING clause filters the data on the group row but not on the individual row.

MySQL GROUP BY and HAVING Clause with Examples, HAVING Clause Purpose Specifies a conditional expression that must be You can specify aggregate operators and DEFAULT functions as HAVING search conditions. You cannot specify LOB columns in the HAVING search condition. NOT NULL with a DEFAULT function compared with a null evaluates to unknown. WHERE Clause filters the records tuple by tuple while HAVING Clause filters the whole group. A query may have both the clauses( WHERE and HAVING Clause). Where Clause applied first and then Having Clause. WHERE Clause restricts records before GROUP BY Clause, whereas HAVING Clause restricts groups after GROUP BY Clause are performed.

Use HAVING and WHERE Clauses in the Same Query, This month's article looks at the GROUP BY clause, and then the difference mysql> SELECT poet,SUM(copies_in_stock) FROM writer GROUP BY poet; 1054 (42S22): Unknown column 'copies_in_stock' in 'having clause'. The following question is not new, but keeps being repeated over time. “How do we select non-aggregate columns in a query with a GROUP BY clause?”. In this post we will investigate this question and try to answer it in a didatic way, so we can refer to this post in the future.

Comments
  • I wonder if the ONLY_FULL_GROUP_BY system parameter would have this effect.