#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    35
    Rep Power
    3

    Using a 'COUNT as' in a 'WHERE'


    Getting an error when trying to return a where from count as daily count....please help
    Code:
       
    SELECT customers.lead_type
    , ac0
    , ac1
    , ac2
    , ac3
    , ac4
    , active
    , daily 
    , timezone
    , run
    , pri
    , customers.id
    , customers.carrier
    , customers.state
    ,customers.customer
    , MAX(xferleads.timestamp)AS maxTimeStamp
    ,COUNT( CASE WHEN xferleads.timestamp >= CURRENT_DATE 
                        AND xferleads.timestamp  < CURRENT_DATE + INTERVAL 1 DAY 
                       AND woactive>0
                       THEN xferleads.customer
                       ELSE NULL END ) AS countdaily
    
    FROM customers 
    LEFT JOIN xferleads ON xferleads.customer = customers.id
    LEFT JOIN fulfillment ON fulfillment.id = customers.id
    
    
    
    WHERE 
         (customers.lead_type='PRE' OR customers.lead_type = 'ANY') 
         AND (ac0 =818 OR ac1 = 818 OR ac2 = 818 OR ac3 = 818 OR ac4 = 818) 
         AND active=1 AND countdaily < (daily * 1.5) 
         AND Tue = 1 
    
    GROUP BY customers.customer
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279
    change this --
    Code:
    WHERE 
         (customers.lead_type='PRE' OR customers.lead_type = 'ANY') 
         AND (ac0 =818 OR ac1 = 818 OR ac2 = 818 OR ac3 = 818 OR ac4 = 818) 
         AND active=1 AND countdaily < (daily * 1.5) 
         AND Tue = 1 
    GROUP BY customers.customer
    to this --
    Code:
     WHERE customers.lead_type IN ('PRE','ANY') 
       AND 818 IN (ac0,ac1,ac2,ac3,ac4) 
       AND active = 1 
       AND Tue = 1 
    GROUP 
        BY customers.customer
         , daily
    HAVING countdaily < (daily * 1.5)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo