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

    Join Date
    Aug 2012
    Posts
    5
    Rep Power
    0

    Math operations on COUNTs


    I want to divide countslw by qtyleads as percent but can't get the syntax:
    SELECT
    fulfillment.index
    , customers.customer
    , customers.id
    , customers.pri
    , customers.active
    , qtyleads
    , priceper
    , pif
    , adjustment
    , wo_date
    , wo_end_date
    , wo_num
    , freeleads
    , woactive
    , xferleads.timestamp
    , vmreps.name
    , vmreps.repid
    , fulfillment.remarks
    , customers.augment

    , COUNT(xferleads.customer) as countalltime

    , COUNT( CASE WHEN xferleads.timestamp >= wo_date
    AND xferleads.timestamp <= COALESCE(wo_end_date,NOW())
    THEN xferleads.customer
    ELSE NULL END ) AS countslw

    , COUNT( CASE WHEN xferleads.timestamp >= CURRENT_DATE
    AND xferleads.timestamp < CURRENT_DATE + INTERVAL 1 DAY

    THEN xferleads.customer
    ELSE NULL END ) AS countdaily

    FROM fulfillment

    LEFT JOIN customers
    ON customers.id = fulfillment.id

    LEFT JOIN xferleads
    ON xferleads.customer = fulfillment.id


    LEFT JOIN vmreps
    ON fulfillment.repid = vmreps.repid

    WHERE woactive = 1

    GROUP BY fulfillment.index
    ORDER BY wo_date
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Code:
    SELECT v.*
         , 100.0 * v.qtyleads / v.countslw AS pct
      FROM ( SELECT fulfillment.index
                  , customers.customer
                  , customers.id
                  , customers.pri
                  , customers.active
                  , qtyleads
                  , priceper
                  , pif
                  , adjustment
                  , wo_date
                  , wo_end_date
                  , wo_num
                  , freeleads
                  , woactive
                  , xferleads.timestamp
                  , vmreps.name
                  , vmreps.repid
                  , fulfillment.remarks
                  , customers.augment
                  , COUNT(xferleads.customer) as countalltime
                  , COUNT( CASE WHEN xferleads.timestamp >= wo_date
                                 AND xferleads.timestamp <= COALESCE(wo_end_date,NOW())
                                THEN xferleads.customer
                                ELSE NULL END ) AS countslw
                  , COUNT( CASE WHEN xferleads.timestamp >= CURRENT_DATE 
                                 AND xferleads.timestamp < CURRENT_DATE + INTERVAL 1 DAY 
                                THEN xferleads.customer
                                ELSE NULL END ) AS countdaily
               FROM fulfillment
             LEFT 
               JOIN customers 
                 ON customers.id = fulfillment.id
             LEFT 
               JOIN xferleads 
                 ON xferleads.customer = fulfillment.id
             LEFT 
               JOIN vmreps 
                 ON fulfillment.repid = vmreps.repid
              WHERE woactive = 1
             GROUP 
                 BY fulfillment.index ) AS v
    ORDER
        BY v.wo_date
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo