Invalid column when using AS

@Sinnbeck this is the query:

Model::selectRaw('count(end_date) as active, (count(*)-count(end_date) as not_active), country, city')
->whereBetween('end_date', [Carbon::now()->subYear(), Carbon::now()])
->where('active', '>', 0)
->groupBy('country', 'city')
->get()

@Ligonsker and the error?

But as I guessed you are using where on an aggregate which isn't possible. Use having

->having('active', '>', 0)

@Sinnbeck This:

[Microsoft][ODBC Driver 17 for SQL Server]Invalid column name 'active' 

@Sinnbeck It still gives me the error 🤯

@Ligonsker can you the query after the update? Also it's always a good idea to mention that you are using mssql :)

@Sinnbeck sorry haha yes I always forget to mention that 😅

query is almost same:

Model::selectRaw('count(end_date) as active, (count(*)-count(end_date) as not_active), country, city')
->whereBetween('end_date', [Carbon::now()->subYear(), Carbon::now()])
->having('active', '>', 0)
->groupBy('country', 'city')
->get()

@Ligonsker try replacing ->get() with ->toSql() and dd the result. Post the output here

@Sinnbeck this is the sql query:

select count(end_date) as active, country, city from [table] where [end_date] between ? and ? group by [country], [city] having [active] > ?

@Ligonsker can you run it in your database manager directly?

@Sinnbeck I ended up doing a different query, I had some other mistakes as well

@Ligonsker ok. Not sure why it didn't work. Haven't had a chance to test it on sqlsrv yet. Close the thread if it's done :)

@Sinnbeck I might test it as well and see the error it gives me in the MSSMS and if yes I will come back to update if not I will close it, didn't want to close it yet because I thought you might also want to know why it didn't work

@Ligonsker Seem sqlsrv does not like to use the named column. So just use the aggregate query

Model::selectRaw('count(*) as active, (count(*)-count(*) as not_active), country, city')
->whereBetween('end_date', [Carbon::now()->subYear(), Carbon::now()])
->havingRaw('count(* '> 0')
->groupBy('country', 'city')
->get()