CODEWITHSUNDEEP
sqlt-sqlsql-server-2008-r2
cojimarmiami
cojimarmiami

Reputation: 659

how to get a percentage depending on a column value?

Update 05/18/15

Having a column with customers names listed on it. How can I get a percentage based in one customer 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

Notice that my column has 12 records, 2 of them are blanks, but they won't count on the percentage, just the ones that has name. I would like to know what percentage represents Sam from the total(in this case 10 records, so Sam % will be 50).

Query should return

Date             Percentage
04/29/15            50
04/30/15            0
05/01/15            0

Update

I don't really care about the other Customers, so lets treat them as one. Just need to know what percentage is Sam from the total list.

Any help will be really appreciated. Thanks

Upvotes: 3

Views: 84

Answers (5)

Stephan
Stephan

Reputation: 6018

Everyone seems to be using subqueries or derived tables. This should perform well and it's easy to follow. Try it out:

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%

Upvotes: 2

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

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

Upvotes: 0

ughai
ughai

Reputation: 9880

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 the count per 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

Upvotes: 0

BrainCoder
BrainCoder

Reputation: 5337

For all the users 

SELECT 
        (COUNT(*) * 100) / (SELECT COUNT(*) FROM Customer WHERE CustomerName <> '') as [percent],
         CustomerName
    FROM Customer group by CustomerName

for specifice user

SELECT 
    (COUNT(*) * 100) / (SELECT COUNT(*) FROM Customer WHERE CustomerName <> '') as [percent],
     CustomerName
FROM Customer where CustomerName ='Sam'

Upvotes: 0

Andomar
Andomar

Reputation: 238078

You could calculate the 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

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

Upvotes: 1

Related Questions