count with inner join sql query

Question

Hi all we have got two tables "Subscriptioninfo , activationinfo" and the structure is like this for the two tables

Subscriptioninfo table

   cert_id (int, PK)
   customer_email(string)
   subscription_key(string)
   activated_on (datetime)

activationinfo Table:

    cert_id (int)
    activationcode(string)
    subscription_key(string , FK)
    activated_ts(datetime)

the below query will give the count as "one" if an entry to Subscriptioninfo with the same year and customer email ID (this is fine)

SELECT COUNT(*) FROM Subscriptioninfo WITH (NOLOCK)
WHERE year(activated_On) = year(getdate()) AND customer_email =@CustomerEmail

we have one sp that will insert the data into both the tables (i.e) one entry to Subscriptioninfo where as four entries to activationinfo

I need to return count as "0" if one entry to subscriptioninfo and two entries to activationinfo

and i need to return count as "1" if one entry to subscriptioninfo and four entries to activationinfo..

Would any one please help on this query how can i get that count with join clause..

Many thanks in advance...

tried with this query but it is giving the activationinfo entry count(i.e) 4 instead of 1

 SELECT COUNT(*) FROM subscriptioninfo csi join activationinfo aci on csi.subscription_key = aci.subscription_key
 WHERE year(Activated_On)  = year(getdate()) AND customer_email = '[email protected]' group by csi.subscription_key

Answer 1

Use CASE statements

SELECT CASE WHEN COUNT(*) = 4 THEN 1
            WHEN COUNT(*) < 4 THEN 0
       END CountResults
FROM subscriptioninfo csi 
join activationinfo aci on csi.subscription_key = aci.subscription_key
WHERE year(Activated_On)  = year(getdate()) AND customer_email = '[email protected]' group by csi.subscription_key