### Thread: Math operations on COUNTs

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. 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```