3

How can I filter out only results when total is greater than n? other words only IPs with more wisits than n (say 500)

I tried ->where('total','>',500) but it didn't work

Thank you

$visits = DB::table('visits')
    ->select('ip_address',DB::raw('count(*) as total'))
    ->where('timestamp', '>=',\Carbon\Carbon::now()->startOfDay())
    ->groupBy('ip_address')
    ->orderBy('total', 'desc')
    ->get();
Vojta
  • 379
  • 1
  • 4
  • 15

1 Answers1

9

WHERE cannot be used on grouped item (such as count(*)) whereas HAVING can. You can refer WHERE vs HAVING question to understand more details,

You have to use having

->having('total', '>', 100)

optionally in your case you can use havingRaw

->havingRaw('count(*) > 2500')
->havingRaw('total > 2500')

ref : https://laravel.com/docs/5.6/queries

Jithin Jose
  • 1,761
  • 2
  • 19
  • 35
  • Unfortunately ```Syntax error or access violation: 1463 Non-grouping field 'total' is used in HAVING clause (SQL: select `ip_address`, count(*) as total from `visits` where `timestamp` >= 2018-02-28 00:00:00 group by `ip_address` having `total` > 100 order by `total` desc)","code":"42000","status_code":500,"debug":``` – Vojta Feb 28 '18 at 09:29
  • havingRaw is great for scenarios where you comparing one field to another summed since the sum result can be a text and interfere with the comparision. – Mitch M Aug 10 '22 at 19:16
  • @Vojta for your case make sure to add each field in your select query to the group by function. – Mitch M Aug 10 '22 at 19:16