CASE statement in SQL giving not proper values

Question

I am having abnormal values when I run this part in my sql code. SQL syntax wise, everything is okay with this?

select
  COUNT(CASE WHEN bt.idBillingStatus = 2  
    THEN 1
        ELSE NULL END)           AS successfulbillinghits,
  SUM(CASE WHEN  bt.idBillingStatus = 2 
    THEN price
      ELSE 0.0 END)  
                  AS old_revenue    
from table

---

Overall Query is this. The result of successfulbillinghits should be equal to timesbilled

SELECT
  cs.idCustomerSubscription,
  cs.msisdn,
  pro.name                       AS promoterName,
  c.name                         AS ClubName,
  c.idClub                       AS ClubID,
  o.name                        AS operatorName,
  o.idOperator                   AS OperatorID,
  co.name                        AS country,
 -- cu.customerSince               AS CustomerSince,
  cs.subscribeddate              AS subscribeddate,
 -- cs.subscriptionNotificationSent AS SubNotificationSent,
 -- cs.eventId                     AS EventId,
  cs.unsubscribeddate           AS unsubscribeddate,
  cs.firstBillingDate            AS FirstBillingDate,
  cs.lastBilledDate              As LastBilledDate,
  cs.lastAttemptDate             AS LastAttemptDate,
  -- smp.code                       AS packageName,
  -- o.mfactor                      AS mmfactor,
  -- cs.idSubscriptionSource        AS SubscriptionChannel,
  -- cs.idUnsubscribeSource         AS UnsubscriptionChannel,
  -- DATE(bt.creationDate)          AS BillingCreationDate,
  -- bt.price                       AS pricePerBilling,
  -- cs.lastRetryDate               As LastRetryDate,
  -- cs.lastRenewalDate             AS LastRenewalDate,
  -- cs.isActive                    AS ActiveStatus,
  -- COUNT(bt.idBillingTransaction) AS BillingAttempts,
  curr.idcurreny_symbol          AS CurrencyID,
  curr.symbol                    AS currency,
  date(bt.creationDate)          AS BillingDate,
  cs.lastBilledAmount            As LastBilledAmount,
  cs.timesbilled,
  price,
 --  sum(price),
  -- revenueShareAmountLocal,
  -- o.mfactor,
  -- count(IFF (bt.idBillingStatus = 2,1,0)) as otherversion,

  count(CASE WHEN bt.idBillingStatus = 2  
    THEN 1
        ELSE 0 END)           AS successfulbillinghits,
  SUM(CASE WHEN  bt.idBillingStatus = 2 
    THEN price 
      ELSE 0.0 END)  
                  AS old_revenue   

FROM
  customersubscription cs
  LEFT JOIN
  billing_transaction bt
    ON CONVERT(cs.msisdn USING latin1) = bt.msisdn 
       AND cs.idClub = bt.idClub
       AND bt.creationDate BETWEEN cs.SubscribedDate AND COALESCE(cs.UnsubscribedDate, now())

  INNER JOIN customer cu ON (cs.idCustomer = cu.idcustomer)
  INNER JOIN operator o ON (o.idoperator = cu.idoperator)
  INNER JOIN country co ON (co.`idCountry` = o.idCountry)
  INNER JOIN curreny_symbol curr ON (curr.idcurreny_symbol = co.idCurrencySymbol)
  LEFT JOIN Promoter pro ON cs.idPromoter = pro.id  
  INNER JOIN club_operator_relationships cor ON cor.clubId = cs.idClub 
  INNER JOIN club c ON c.idClub = cs.idClub
  -- INNER JOIN operator op ON op.idOperator = cu.idOperator


WHERE
-- (cs.timesbilled > 0 and cs.subscribeddate < '2016-09-01 00:00:00' ) 
cs.subscribeddate between '2017-04-20 00:00:00' and '2017-04-21 00:00:00'
  AND cs.idClub IN (39) 
GROUP BY idCustomerSubscription, ClubName, operatorName, promoterName

Successfulbillinghits is much greater than timesbilled in the result

Answer 1

Instead of using CASE, you can use WHERE clause with these aggregate functions, e.g.:

SELECT COUNT(*) as `successfulbillinghits`, SUM(price) as `old_revenue`
FROM table bt
WHERE bt.idBillingStatus = 2;

Answer 2

Instead of COUNTuse SUM, as count counts blanks or nulls also

  select
  SUM(CASE WHEN bt.idBillingStatus = 2  
    THEN 1
        ELSE 0 END)           AS successfulbillinghits,
  SUM(CASE WHEN  bt.idBillingStatus = 2 
    THEN price
      ELSE 0.0 END)  
                  AS old_revenue    
from table