Multiple count from the same field

[zx10r]

I have been having some trouble with the query below.

select a.advisor_ao as 'AO',
case c.existing_client when 'feps' then count(c.existing_client) else 0 end as 'total'
from advisor_tb a left join company_tb b on a.advisor_id = b.advisor_id
left join comp_seminar_ca_tb c on b.company_id = c.company_id
group by a.advisor_ao

I am not looking for an answer just wanted to know if my query is incorrect because if it is not thean it's my table structure

 

[TonyGroves]

If I understand your requirements, it looks like your CASE expression should replaced with:
[tt]
COUNT(
CASE WHEN c.existing_client='feps' THEN 1 ELSE 0 END
)
[/tt]
or, simpler:[tt]
COUNT(c.existing_client='feps')

 

[zx10r]

Well i still get the same result. What is weird is that if i execute this statement only for this table i get the right results. However once i start joining other tables i get bad results.

So the one below works fine"
select prospect_id as 'ID',
CONCAT_WS(' ',prospect_fn,prospect_ln) as 'Name',
case when existing_client = 'other' then count(existing_client) else 0 end as 'total'
from comp_seminar_ca_tb
group by prospect_id