CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
SkysLastChance
SkysLastChance

Reputation: 221

Percentage of 2 @@RowCounts

I am trying to get the Percentage of these two tables row count. Ideally I would like just to see the percentage and nothing else.

This is what I have tried with no luck. 

Select Distinct ID
from dbo.1
Where StartDate between '2014-04-01' and '2017-04-30'
and Status  = 'ATTENDED'
order by ID

SELECT @@ROWCOUNT AS 'Rowcount1'


Select Distinct ID
from dbo.1
Where Acitve = 'Y'
and StartDate between '2014-04-01' and '2017-04-30'
and Status = 'ATTENDED'
order by EmrID

SELECT @@ROWCOUNT AS 'RowCount2'

SELECT 'RowCount1' / 'RowCount2' AS Percentage

Upvotes: 0

Views: 63

Answers (3)

David
David

Reputation: 3763

If you just want percent try this:

SELECT 
       ( 
              SELECT CONVERT(FLOAT, Count(DISTINCT id))
              FROM   dbo.1 
              WHERE  startdate BETWEEN '2014-04-01' AND    '2017-04-30' 
              AND    status = 'ATTENDED') / 
       ( 
              SELECT CONVERT(FLOAT, count(DISTINCT id))
              FROM   dbo.1 
              WHERE  acitve = 'Y' 
              AND    startdate BETWEEN '2014-04-01' AND    '2017-04-30' 
              AND    status = 'ATTENDED') AS percentage

If you also want the individual values try this:

DECLARE @val1 INT;
DECLARE @val2 INT;

SELECT @val1 = Count(DISTINCT id) 
FROM   dbo.1 
WHERE  startdate BETWEEN '2014-04-01' AND    '2017-04-30' 
AND    status = 'ATTENDED'

SELECT @val2 = count(DISTINCT id) 
FROM   dbo.1 
WHERE  acitve = 'Y' 
AND    startdate BETWEEN '2014-04-01' AND    '2017-04-30' 
AND    status = 'ATTENDED'

SELECT @val1 AS val1, @val2 AS val2, CONVERT(FLOAT, @val1) / CONVERT(FLOAT, @val2) AS percent

The order by statements aren't needed as all you care about is the counts.

This can be reduced using a CTE too:

;WITH

base AS (
    SELECT id, acitve
    FROM   dbo.1 
              WHERE  startdate BETWEEN '2014-04-01' AND    '2017-04-30' 
              AND    status = 'ATTENDED'
)

SELECT 
       ( 
              SELECT CONVERT(FLOAT, Count(DISTINCT id))
              FROM   base) / 
       ( 
              SELECT CONVERT(FLOAT, count(DISTINCT id))
              FROM   base
              WHERE  acitve = 'Y') AS percentage

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

If you don't need select distinct, then SQLZim's answer is correct. If you do, then you need to tweak the answer. Here is one way:

select count(*) as RowCount
       count(distinct case when Active = 'Y' then id end) as ActiveCount,
       count(distinct case when Active = 'Y' then id end) * 1.0 / count(distinct id) 
from dbo.1
where StartDate >= '2014-04-01' and StartDate <= '2017-04-30' and
      Status = 'ATTENDED';

Upvotes: 3

SqlZim
SqlZim

Reputation: 38033

Why are you trying to use @@rowcount like that? How about some conditional aggregation instead?

select 
    AllCount    = count(*)
  , ActiveCount = count(case when Active='Y' then 1 end)
  , Percentage  = avg(case when Active='Y' then 1.0 else 0.0 end)
from dbo.t
where StartDate >= '2014-04-01' 
  and StartDate <= '2017-04-30'
  and Status = 'ATTENDED'

Also, be careful with between. What do between and the devil have in common? - Aaron Bertrand

Upvotes: 4

Related Questions