How do I get the percentage based on the column value?
Update 05/18/15
Having a column with customer names listed on it. How to get a percentage of one client per date? for example
CustomerName Date
Sam 04/29/15
Joy 04/29/15
Tom 04/29/15
Sam 04/29/15
Oly 04/29/15
Joy 04/29/15
04/29/15
Sam 04/29/15
04/29/15
Sam 04/29/15
Oly 04/29/15
Sam 04/29/15
Oly 04/30/15
Joy 05/01/15
Note that there are 12 records in my column, 2 of them are spaces, but they will not count for percentage, only those that have a name. I would like to know what percentage Sam represents of the total (10 records in this case, so Sam% would be 50).
The request should return
Date Percentage
04/29/15 50
04/30/15 0
05/01/15 0
Update
I don't like other clients, so we treat them as one. You just need to know what percentage Sam is from the general list.
Any help would be really appreciated. thank
source to share
Everyone seems to be using subqueries or views. This should work well and be easy to follow. Try:
DECLARE @CustomerName VARCHAR(5) = 'Sam';
SELECT [Date],
@CustomerName AS CustomerName,
percentage = CAST(CAST(100.0 * SUM(CASE WHEN CustomerName = @CustomerName THEN 1 ELSE 0 END)/COUNT(*) AS INT) AS VARCHAR(20)) + '%'
FROM @yourTable
WHERE CustomerName != ''
GROUP BY [Date]
Results:
Date CustomerName percentage
---------- ------------ ---------------------
2015-04-29 Sam 50%
2015-04-30 Sam 0%
2015-05-01 Sam 0%
source to share
You can calculate numbers per person + day in a subquery:
select Date
, CustomerName
, 100.0 * cnt / sum(cnt) over (partition by date)
from (
select Date
, CustomerName
, count(*) cnt
from table1
where CustomerName <> ''
group by
Date
, CustomerName
) t1
Prints:
Date CustomerName
----------------------- ------------ ---------------------------------------
2015-04-29 00:00:00.000 Joy 20.000000000000
2015-04-29 00:00:00.000 Oly 20.000000000000
2015-04-29 00:00:00.000 Sam 50.000000000000
2015-04-29 00:00:00.000 Tom 10.000000000000
(4 row(s) affected)
source to share
you would do something like this
SELECT (COUNT(*) * 100) / (SELECT COUNT(*) FROM Customer WHERE CustomerName <> '' AND Date = '04/29/15')
FROM Customer
WHERE CustomerName = 'Sam'
AND Date = '04/29/15'
If you want to get count
past the date you can use this
SELECT T.Date,((ISNULL(CustomerCount,0) * 100) / TotalCount)
FROM
(
SELECT COUNT(*) as TotalCount,Date
FROM Customer
WHERE CustomerName <> ''
GROUP BY Date
)T
LEFT JOIN
(
SELECT COUNT(*)
FROM Customer
WHERE CustomerName = 'Sam'
GROUP BY Date
)C
ON T.Date = C.Date
source to share
For all users
SELECT
(COUNT(*) * 100) / (SELECT COUNT(*) FROM Customer WHERE CustomerName <> '') as [percent],
CustomerName
FROM Customer group by CustomerName
for a specific user
SELECT
(COUNT(*) * 100) / (SELECT COUNT(*) FROM Customer WHERE CustomerName <> '') as [percent],
CustomerName
FROM Customer where CustomerName ='Sam'
source to share