CODEWITHSUNDEEP
sql-server
DevDave
DevDave

Reputation: 6888

Invalid column name in SQL query

I'm a bit rusty in writing SQL Queries, I'm trying to output a list of 'Contacts' with no 'PAs'

select a.first_name, a.last_name, (select count(b.contact_id)) as "PA Count" 
from CRM_Contact a left join CRM_PA b on a.id = b.contact_id 
where [PA Count] = 0 
group by a.first_name, a.last_name

Could someone explain why I am getting the error message:

Invalid column name 'PA Count'.

Upvotes: 0

Views: 176

Answers (3)

Buğra Kömürcü
Buğra Kömürcü

Reputation: 1

You forgot to use From in the following query:

(select count(b.contact_id) from YourTable)

Upvotes: -1

StepUp
StepUp

Reputation: 38094

If you would like to COUNT then just use column name without SELECT statement and use HAVING statement after GROUP BY:

select 
  a.first_name
, a.last_name
, Count(b.contact_id) as "PA Count" 
from CRM_Contact a 
left join CRM_PA b on a.id = b.contact_id 
group by a.first_name, a.last_name
HAVING Count(b.contact_id) = 0

Upvotes: 2

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131189

WHERE can only use columns or their aliases. PA Count though is an aggregate that gets calculated after WHERE.

Filtering aggregates is performed with the HAVING clause :

SELECT a.first_name, 
       a.last_name, 
       COUNT(b.contact_id) as [PA Count]
FROM CRM_Contact a 
       LEFT JOIN CRM_PA b ON a.id = b.contact_id 
GROUP BY a.first_name, 
          a.last_name 
HAVING Count(b.contact_id) =0

Upvotes: 1

Related Questions