Jeremy Kirkbride
asked on
SQL % Calculation
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'
FROM CONTACT2
WHERE UPPNVERIFY like '201%'
SELECT COUNT(ACCOUNTNO)AS 'Active Customers'
FROM CONTACT2
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]
FROM (
SELECT
--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]
FROM CONTACT2
WHERE
(UPPNVERIFY like '2015%') OR
(USALESYTD IS NOT NULL AND USALES2014 IS NOT NULL)
) AS subquery1
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'
FROM CONTACT2
WHERE UPPNVERIFY like '201%'
SELECT COUNT(ACCOUNTNO)AS 'Active Customers'
FROM CONTACT2
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]
FROM (
SELECT
--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]
FROM CONTACT2
WHERE
(UPPNVERIFY like '2015%') OR
(USALESYTD IS NOT NULL AND USALES2014 IS NOT NULL)
) AS subquery1
Shaun Kline
Your WHERE clause uses (UPPNVERIFY like '2015%') which may be excluding results you are expecting with just (UPPNVERIFY like '201%').
ASKER
Gotcha, changed the statement to 201%, the percentage is much closer but still calculating 59.6, math has never been my strong point, but I get 57% from 186 out of 325
SELECT COUNT(uppnverify) AS 'Verified GM Customers'
FROM CONTACT2
WHERE UPPNVERIFY like '201%'
SELECT COUNT(ACCOUNTNO)AS 'Active Customers'
FROM CONTACT2
WHERE USALESYTD IS NOT NULL AND USALES2014 IS NOT NULL
SELECT CAST([Verified Customers] * 100.0 / [Active Customers] AS decimal(4, 1)) AS [%age of GM verified customers]
--, [Verified Customers], [Active Customers]
FROM (
SELECT
--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]
FROM CONTACT2
WHERE
(UPPNVERIFY like '201%') OR
(USALESYTD IS NOT NULL AND USALES2014 IS NOT NULL)
) AS subquery1
SELECT COUNT(uppnverify) AS 'Verified GM Customers'
FROM CONTACT2
WHERE UPPNVERIFY like '201%'
SELECT COUNT(ACCOUNTNO)AS 'Active Customers'
FROM CONTACT2
WHERE USALESYTD IS NOT NULL AND USALES2014 IS NOT NULL
SELECT CAST([Verified Customers] * 100.0 / [Active Customers] AS decimal(4, 1)) AS [%age of GM verified customers]
--, [Verified Customers], [Active Customers]
FROM (
SELECT
--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]
FROM CONTACT2
WHERE
(UPPNVERIFY like '201%') OR
(USALESYTD IS NOT NULL AND USALES2014 IS NOT NULL)
) AS subquery1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Another WHERE Clause discrepancy:
SUM(CASE WHEN USALESYTD IS NOT NULL AND USALES2013 IS NOT NULL THEN 1 ELSE 0 END)
versus:
(USALESYTD IS NOT NULL AND USALES2014 IS NOT NULL)
Looks like your result set may not be returning the records you are expecting.
SUM(CASE WHEN USALESYTD IS NOT NULL AND USALES2013 IS NOT NULL THEN 1 ELSE 0 END)
versus:
(USALESYTD IS NOT NULL AND USALES2014 IS NOT NULL)
Looks like your result set may not be returning the records you are expecting.
ASKER
Thanks so much. All good now