# 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

+3

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

```
+2

source

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

```
+1

source

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

```
0

source

You can try this:

``````select name, date, (100.0 * count(*)) /
(select count(*) from table t2
where name is not null and name <> '' and t2.date = t1.date)
from table t1
where name is not null and name <> ''
group by name, date
```

```
0

source

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

```
0

source

All Articles