SQL % Calculation
Posted on 2015-02-06
have a very basic qry that counts number of active customers vs those customers that have been verified with current data and shows the percent of "verified" accounts.
The date is showing 186 verified out of 325 Active, but my percent results show 39.42 not 57.23
SELECT COUNT(uppnverify) AS 'Verified GM Customers'
WHERE UPPNVERIFY like '201%'
SELECT COUNT(ACCOUNTNO)AS 'Active Customers'
WHERE USALESYTD IS NOT NULL AND USALES2014 IS NOT NULL
SELECT CAST([Verified Customers] * 100.0 / [Active Customers] AS decimal(4, 2)) AS [%age of GM verified customers]
--, [Verified Customers], [Active Customers]
--count number of GM company with verified field
SUM(CASE WHEN UPPNVERIFY like '201%' THEN 1 ELSE 0 END) AS [Verified Customers],
-- count number of GM company with active status (sales in last 2 yr)
SUM(CASE WHEN USALESYTD IS NOT NULL AND USALES2013 IS NOT NULL THEN 1 ELSE 0 END) AS [Active Customers]
(UPPNVERIFY like '2015%') OR
(USALESYTD IS NOT NULL AND USALES2014 IS NOT NULL)
) AS subquery1