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'.
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
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
You forgot to use From in the following query:
(select count(b.contact_id) from YourTable)
SELECT without a FROM is valid syntax; although it is odd in the context used. This, for example, will return the "expected" results. SELECT I, (SELECT N) N FROM (VALUES(1,2),(3,4))V(I,N);
WHERE, you need to useHAVING